Page 1 of 1

Is it possible to use two COUNT()'s?

Posted: Mon Jan 08, 2007 10:34 am
by seodevhead
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:

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.id
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.

Posted: Mon Jan 08, 2007 10:55 am
by acidHL
Yes, I think this should be possible using sub queries.

Code: Select all

SELECT table1.*, (SELECT COUNT(id) FROM table2) as table2count, (SELECT COUNT(id) FROM table3) as table3count FROM table1;

Posted: Mon Jan 08, 2007 11:05 am
by feyd

Code: Select all

...

Posted: Mon Jan 08, 2007 11:58 am
by seodevhead
Thanks a bunch acidHL! I really appreciate it... it works!!! :)