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.
";

Answer 1

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*/