How can I group by count?

[angiegail77]

Example: I want to know how many clients were seen during a time period grouped by total number of sessions then sorted by how many clients had a total of a certain number of sessions.

For example: Between 7-01-2010 and 6-30-2011

Total Number of Session Number of clients
1 30
2 26
3 41
4 18

using fields client, activity, activitydate

 

[lbass]

What version of CR are you using, and what type is your database?

-LB

 

[angiegail77]

Crystal XI (11) - OLE DB (ADO)

Thanks, Angie

 

[lbass]

But what database? Access? Excel? Do you have the option of adding commands or SQL expressions?

-LB

 

[angiegail77]

Oh, sorry. We use SQL

 

[lbass]

If you can use a command, do that so you can add parameters for the dates. In the "add command" area, create two parameters {?Start} and {?End} and then set up the command like this:

select table.client, table.activity, table.activitydate,
(
select count(A.activity)
from table A
where A.activitydate >= {?Start} and
A.activitydate < {?End}+1 and
A.client = table.client
) as activitycount
from table
where table.activitydate >= {?
Start} and
table.activitydate < {?End}+1

Correct the punctuation and syntax for your database. In the subselect, replace "table" with your table name, but leave the alias "A" as is, substituting only the correct field names. Then you can group on {command.activitycount} and insert a count on client at the group level.

-LB

 

[angiegail77]

Sorry, SQL server