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 :
x
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;