[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:
USERS
========
UserID
AgencyID
SignUpDate
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!
Sarah
More information about the thelist
mailing list