CODEWITHSUNDEEP
sqlsql-servert-sql
ordiminnie
ordiminnie

Reputation: 127

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?

Upvotes: 1

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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;

Upvotes: 3

Related Questions