[thelist] selecting two COUNT()s in one MySQL query
Sarah
poohbear at designshift.com
Tue Sep 2 07:46:00 CDT 2003
>hi sarah
>
>yes, you can do this in one query
>
>your use of COUNT(u.UserID) instead of COUNT(*) suggests that you want a
>left outer join from Agencies to Users, to include all agencies, even
>those with no users -- change this to inner if you want only agencies with
>Total > 0
>
> select a.Agency
> , sum(
> case when month(u.SignUpDate) = 8
> and year(u.SignUpDate) = 2003
> then 1
> else 0
> end ) as Aug2003
> , count(u.UserID) as Total
> from Agencies a
> left outer
> join Users u
> on a.AgencyID = u.AgencyID
> group
> by a.Agency
>
>SUM() adds up a series of 1's and 0's to give you the count for august
>
>note that you should always GROUP BY the same non-aggregate columns as you
>use in the SELECT list
Just wanted to say thanks very much for your help with this, Rudy, your
solution worked perfectly!
Sarah
More information about the thelist
mailing list