Display zero by using count(*) if no result returned for a particular case

Question

I have a query like this that returns number of rows for each case in city .

select 
    case edition_id 
        when 6 then 'DELHI' 
        when 50 then 'AHMEDABAD' 
        when 4 then 'HYDERABAD' 
        when 25 then 'KOLKATA' 
        when 51 then 'BANGALORE' 
        when 5 then 'MUMBAI' 
        when 24 then 'CHENNAI' 
    end as CITY,
    count(*) as Total 
from #tmptab1
group by edition_id

drop table #tmptab1

The result comes out to be like

CITY    Total
MUMBAI  1
DELHI   28
CHENNAI 1
KOLKATA 35
AHMEDABAD 3

So if there is no rows returned from a city , that city is omitted in final result

I want result as

CITY    Total
MUMBAI  1
DELHI   28
CHENNAI 1
KOLKATA 35
AHMEDABAD 3
BANGALORE 0 -- if no result from bangalore display zero.

How to do this ?

I tried

case count(*)>0 then count(*) else 0 end as Total 

but it does not work

Answer 1

I would insert the cities into a temporary table, then do a LEFT JOIN with the grouping query as follows:

CREATE TABLE #cities (edition_id INT, city VARCHAR(16))
INSERT INTO #cities VALUES(6, 'DELHI')
INSERT INTO #cities VALUES(50, 'AHMEDABAD')
INSERT INTO #cities VALUES(4, 'HYDERABAD')
INSERT INTO #cities VALUES(25, 'KOLKATA')
INSERT INTO #cities VALUES(51, 'BANGALORE')
INSERT INTO #cities VALUES(5, 'MUMBAI')
INSERT INTO #cities VALUES(24, 'CHENNAI')

select 
    c.city 'City', 
    ISNULL(t.Total, 0) 'Total'
from 
    #cities c
    LEFT JOIN (
        SELECT 
            edition_id, count(*) as Total 
        #tmptab1 
        GROUP BY edition_id
    ) AS t
    ON c.edition_id = t.edition_id

drop table #tmptab1
drop table #cities

BTW, it would make sense to have #cities as a normal table so that you don't need to create it everytime the query runs.

Answer 2

The problem is that you are grouping by edition_id. If there is no edition_id in your result then it can't count it.

What you can do instead is select all the cities out with their edition id, left join it to the counts and then do an isnull:

WITH CITIES AS
(
        SELECT 6 AS edition_id, 'DELHI' As CityName
        UNION
        SELECT 50, 'AHMEDABAD'
        UNION
        ....
)
SELECT c.cityname, isnull(counts.total,0) as total
FROM CITIES
LEFT JOIN (SELECT edition_id, count(*) as Total #tmptab1 group by edition_id) counts ON counts.edition_id = CITIES.edition_id