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;