Display Zero By Using Count(*) If No Result Returned For A Particular Case

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' whe

Solution 1:

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

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

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

droptable #tmptab1
droptable #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.

Solution 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
(
        SELECT6AS edition_id, 'DELHI' As CityName
        UNION
        SELECT50, 'AHMEDABAD'
        UNION
        ....
)
SELECT c.cityname, isnull(counts.total,0) as total
FROM CITIES
LEFT JOIN (SELECT edition_id, count(*) as Total #tmptab1 groupby edition_id) counts ON counts.edition_id = CITIES.edition_id