Is it possible to use two COUNT()'s?
Posted: Mon Jan 08, 2007 10:34 am
I am trying to make one single query that counts (using the COUNT() aggregate function in MySQL) how many total records there are meeting certain requirements from numerous tables... however, I notice I have to use GROUP BY for the COUNT(column) function to work. But because I can't use multiple GROUP BY's... it seems that I can only use COUNT() once in the query... is this true?
Example of my query:
Basically I want the query to return how many records there are in table1 that meet the WHERE requirements... return how many total records there are in table2, and so on.... it is only returning one or the other... depending on which one I group it by.
Is it possible to use COUNT() multiple times in a one query for multiple tables? Thanks for your help.
Example of my query:
Code: Select all
SELECT COUNT(cc.id) AS num_recs, COUNT(dip.id) AS num_dips, DATEDIFF(NOW(), MIN(cc.start)) AS totalDays FROM table1 AS cc, table2 as dip WHERE cc.status='1' OR cc.status='2' GROUP BY cc.idIs it possible to use COUNT() multiple times in a one query for multiple tables? Thanks for your help.