Group By and Count problem

[murfitUK 0]

My brain has gone to sleep and I cannot work out how to write this simple query! Note, this is for mysql.

 

I have a table called `calls` which is used to collect telephone call information. Some of the fields are:

`id`

`client_id`

`date`

There are others such as what the call was about, but that's not important right now.

 

I can get the total number of phone calls received during a specified period with the query:

SELECT COUNT(*) AS `total` FROM `calls` WHERE `date` BETWEEN '{$rsd}' AND '{$red}'

[rsd and red are report start date and report end date]

 

So far, so good. My problem is that I now need to find out how many clients made 1 phone call, how many made 2 calls, how many made 3 calls, etc. I do not need to know which clients called 3 times - just the total number of clients. The output of the query will be displayed something like:

 

241 clients called 1 time(s)

187 clients called 2 time(s)

45 clients called 3 time(s)

38 clients called 4 time(s)

... etc

 

I've tried many combinations of count, distinct, group by etc but only succeed in getting myself even more confused. I know there is an easy way to do this but I can't find it!

 

If anyone can help I would be very thankful.

[justsomeguy 1,135]

You might want to use a subquery. The inner query would get the number of times that each client called, and the outer one would group by the number of calls.