[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