return count 0 with mysql group by

Question

database table like this

============================
= suburb_id   |   value
= 1           |    2
= 1           |    3
= 2           |    4
= 3           |    5

query is

SELECT COUNT(suburb_id) AS total, suburb_id 
  FROM suburbs 
 where suburb_id IN (1,2,3,4) 
GROUP BY suburb_id

however, while I run this query, it doesn't give COUNT(suburb_id) = 0 when suburb_id = 0 because in suburbs table, there is no suburb_id 4, I want this query to return 0 for suburb_id = 4, like

============================
= total       |   suburb_id
= 2           |    1
= 1           |    2
= 1           |    3
= 0           |    4

Answer 1

A GROUP BY needs rows to work with, so if you have no rows for a certain category, you are not going to get the count. Think of the where clause as limiting down the source rows before they are grouped together. The where clause is not providing a list of categories to group by.

What you could do is write a query to select the categories (suburbs) then do the count in a subquery. (I'm not sure what MySQL's support for this is like)

Something like:

SELECT 
  s.suburb_id,
  (select count(*) from suburb_data d where d.suburb_id = s.suburb_id) as total
FROM
  suburb_table s
WHERE
  s.suburb_id in (1,2,3,4)

(MSSQL, apologies)

Answer 2

Query:

select case
         when total is null then 0
         else total
       end as total_with_zeroes,
       suburb_id
from (SELECT COUNT(suburb_id) AS total, suburb_id 
        FROM suburbs 
       where suburb_id IN (1,2,3,4) 
    GROUP BY suburb_id) as dt

Answer 3

This:

SELECT  id, COUNT(suburb_id)
FROM    (
        SELECT  1 AS id
        UNION ALL
        SELECT  2 AS id
        UNION ALL
        SELECT  3 AS id
        UNION ALL
        SELECT  4 AS id
        ) ids
LEFT JOIN
        suburbs s
ON      s.suburb_id = ids.id
GROUP BY
        id

or this:

SELECT  id,
        (
        SELECT  COUNT(*)
        FROM    suburb
        WHERE   suburb_id = id
        )
FROM    (
        SELECT  1 AS id
        UNION ALL
        SELECT  2 AS id
        UNION ALL
        SELECT  3 AS id
        UNION ALL
        SELECT  4 AS id
        ) ids

This article compares performance of the two approaches:

• Aggregates: subqueries vs. GROUP BY

, though it does not matter much in your case, as you are querying only 4 records.