Show zero with count (*) if the result is not returned for a specific case

Question

I have a query that returns the number of rows for each case in the 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 

Result looks like

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

So, if the city has no rows returned from the city, this city is omitted in the end result

I want a result like

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

How to do it?

I tried

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

but it does not work

Answer 1

I would enter the cities in the temporary table, then I would do a LEFT JOIN with a grouping request 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 

By the way, it makes sense to have #cities as a regular table, so you do not need to create it every time a query is executed.

Answer 2

The problem is that you are grouped by version_id. If your result does not have a version_id value, it cannot count.

Instead, you can select all cities with their version identifier, leave it in the list, and then make 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