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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

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

Post 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.
acidHL
Forum Commoner
Posts: 41
Joined: Wed Dec 07, 2005 7:38 am

Post 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;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

...
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

Thanks a bunch acidHL! I really appreciate it... it works!!! :)
Post Reply