Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
                Do you have another table with information for all the suburbs?  Or is surburb 4 Sir Not-Appearing-In-This-Database?
– Powerlord
                Aug 30, 2010 at 14:06
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
  suburb_table s
WHERE
  s.suburb_id in (1,2,3,4)
(MSSQL, apologies)
                +1 for simple case, but when you need to combine several more complicated queries, please see my answer.
– Upgradingdave
                Aug 24, 2012 at 2:26
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.
                Puke. But correct. Mysql has so many "no standard" funky features, how come it doesn't have a series generator yet?! btw, for at least some code brevity, you can remove the ALL from your UNIONs (all values are unique and already ordered, so the result will be the same with just UNION) and remove all but the first AS ID. And you could in-line it: FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ids.
– Bohemian
                Aug 24, 2012 at 3:39
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
                Because there are no records with suburb_id being 4, the inner query won't return a NULL for the CASE expression to capture.
– OMG Ponies
                Aug 30, 2010 at 3:11
@geofftnz's solution works great if all conditions are simple like in this case. But I just had to solve a similar problem to generate a report where each column in the report is a different query. When you need to combine results from several select statements, then something like this might work. 
You may have to programmatically create this query. Using left joins allows the query to return rows even if there are no matches to suburb_id with a given id. If your db supports it (which most do), you can use IFNULL to replace null with 0:
select IFNULL(a.count,0), IFNULL(b.count,0), IFNULL(c.count,0), IFNULL(d.count,0)
from (select count(suburb_id) as count from suburbs where id=1 group by suburb_id) a,
 left join (select count(suburb_id) as count from suburbs where id=2 group by suburb_id) b on a.suburb_id=b.suburb_id
 left join (select count(suburb_id) as count from suburbs where id=3 group by suburb_id) c on a.suburb_id=c.suburb_id
 left join (select count(suburb_id) as count from suburbs where id=4 group by suburb_id) d on a.suburb_id=d.suburb_id;
The nice thing about this is that (if needed) each "left join" can use slightly different (possibly fairly complex) query.
Disclaimer: for large data sets, this type of query might have not perform very well (I don't write enough sql to know without investigating further), but at least it should give useful results ;-)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.