Skip to content
Advertisement

How to display rows with no count aggregate result?

I have a table which contains Job, City and PersonName (who works in that city). I want to return all jobs and cities even if there is no one working in that city. This is a simple example :

Creation of the table :

CREATE TABLE #Data
(
    Job varchar(50),
    City varchar(50),
    PersonName varchar(50)
)

INSERT INTO #Data
VALUES 
    ('Teacher', 'New-York', 'John')
    ,('Teacher', 'Los-Angeles', 'Mary')
    ,('Fireman', 'New-York', 'Sam')

My Query

SELECT 
    [Job]
    , [City]
    ,COUNT(PersonName) AS NumberOfPeopleWorkingThere
FROM #Data 
GROUP BY [Job], [City]
ORDER BY [Job], [City]

My result

Fireman    New-York      1
Teacher    Los-Angeles   1
Teacher    New-York      1

But I would like to have :

Fireman    Los-Angeles   0    -> this row in addition
Fireman    New-York      1
Teacher    Los-Angeles   1
Teacher    New-York      1

I have no clue how to do this, could you help me please?

Advertisement

Answer

This answers the original version of the question.

Use cross join to generate the rows and then left join:

SELECT j.job, c.city,
       COUNT(PersonName) AS NumberOfPeopleWorkingThere
FROM (SELECT DISTINCT job FROM #Data) j CROSS JOIN
     (SELECT DISTINCT city FROM #Data) c LEFT JOIN
     #Data d
     ON d.job = j.job AND d.city = c.city
GROUP BY j.job, c.city
ORDER BY j.job, c.city;
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement