[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