[thelist] selecting two COUNT()s in one MySQL query

rudy rudy937 at rogers.com
Fri Aug 29 09:42:34 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

rudy



More information about the thelist mailing list