quick SQL question

[zimu]

hey guys. quick question.

got 5 tables. each has a field called "status." status is downloading, uploading or done.

whats the easiest way i can return a count of the AGGREGATE "downloading" records, "uploading" records and "done" records?

i know there has to be some way to do this without 5 queries, each saying count(*) from tableA where status = 'downloading' + count(*) from tableB.....


any help?

 

[MrChad]

(SELECT status AS TableAStatus, count(*) AS TableACount from TableA group by status)
UNION
(SELECT status AS TableBStatus, count(*) AS TableBCount from TableB group by status)
UNION
(SELECT status AS TableCStatus, count(*) AS TableCCount from TableC group by status)
UNION
(SELECT status AS TableDStatus, count(*) AS TableDCount from TableD group by status)
UNION
(SELECT status AS TableEStatus, count(*) AS TableECount from TableE group by status)

 

[shutterFly]

Try This:

SELECT COUNT(*) AS StatusCount, Status
FROM
(
SELECT Status FROM TableA
UNION ALL
SELECT Status FROM TableB
UNION ALL
SELECT Status FROM TableC
UNION ALL
SELECT Status FROM TableD
UNION ALL
SELECT Status FROM TableE
)
GROUP BY Status

What MrChad suggested will get you the counts but they'll be separated out by tables.