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

Sarah poohbear at designshift.com
Fri Aug 29 09:16:18 CDT 2003

I am working on some stats reports for a site I've just "finished" working 
on. I have a Users table like this:

And I'd like to end up with a report listing users per agency, in one 
column showing the ones who signed up in a particular month, and in another 
column showing the total, like this:
   AGENCY   AUG 2003   TOTAL
   ABC      10         290
   DEF      15         314
   GHI      9          202

I've got two separate queries to get the total agency numbers and the per 
month agency numbers, but I'm wondering if it's possible to get all this 
data in one query:
   SELECT COUNT(u.UserID) AS UserCount, a.Agency
   FROM Users u, Agencies a
   WHERE u.AgencyID = a.AgencyID
   AND MONTH(u.SignUpDate) = 8
   AND YEAR(u.SignUpDate) = 2003
   GROUP BY a.AgencyID

I've been told that using an explicit JOIN might work for this, but I'm not 
really sure how that would work. I've looked at the MySQL reference, but it 
doesn't really seem to be written for non-DBA's, if you know what I mean.

Many thanks for any insight anyone can provide into this query!


More information about the thelist mailing list