Multiple counts in one query

Question

This query doesn't work. Can someone help me please?

$query = "SELECT 
    COUNT(WHERE Name='george') AS george_total,
    COUNT(WHERE Name='michael') AS michael_total,
    COUNT(WHERE Name='mike') AS mike_total 
FROM users WHERE Banned IS NOT '1' AND Active='yes' AND Logins>1 AND Registered_to_forum='1'";

$row=mysql_fetch_array($result);
echo "
    We found $row['george_total'] people with the name 'George' in our database,
    $row['michael_total'] with the name Michael
    and $row['mike_total'] with the name Mike.
";

score 8 · Accepted Answer · answered Nov 19 '11 at 10:36

8

You can use a CASE statement and either COUNT or SUM.

The COUNT version is below. COUNT only counts NOT NULL values so you can use any Non Null column or constant instead of 1.

SELECT 
    COUNT(CASE WHEN Name='george' THEN 1 END) AS george_total,
    COUNT(CASE WHEN Name='michael' THEN 1 END) AS michael_total,
    COUNT(CASE WHEN Name='mike' THEN 1 END) AS mike_total /*.... rest of query*/

The SUM version is

SELECT 
    SUM(CASE WHEN Name='george' THEN 1 ELSE 0 END) AS george_total,
    SUM(CASE WHEN Name='michael' THEN 1 ELSE 0 END) AS michael_total,
    SUM(CASE WHEN Name='mike' THEN 1 ELSE 0 END) AS mike_total /*.... rest 
                                                                   of query*/

answered Nov 19 '11 at 10:36

Martin Smith

438,706
87
741
845

Thanks, but it doesn't return the amount of for example george's, it only returns 1 or 0. Not 10 george's or something like that. – Jordy Nov 19 '11 at 10:49
@Jordy - You must only have one george then. Both queries work fine. Maybe you are excluding them because of your `where` clause? – Martin Smith Nov 19 '11 at 10:50
Saved me a ton of SQL execution time! Thanks! :) – Nathan Pitman Aug 01 '13 at 16:14

Multiple counts in one query

1 Answers1