CODEWITHSUNDEEP
sql-servergroup-bycount
Bilal Sheikh
Bilal Sheikh

Reputation: 119

How to count number of tuples with a specific value per group?

Consider the following schema:

Game(gameID, name, developID, cost, category),
Developer(developerID, name, country, speciality )

Now I want to find out number of games developed by American developer in each category. My attempt is as follows:

select category, count(developID) as TotalGames
from Game join Developer on developerID = developID
where country = 'America'
group by category;

Now the problem here is that this query does not show the categories in which no American developer has developed any game. But I want it to show that category with TotalGames = 0

So how would I do that?

Upvotes: 0

Views: 170

Answers (1)

Charlieface
Charlieface

Reputation: 72153

Not sure why the other answer uses a CTE, it is not necessary. You can filter directly in the join clause.

You need to change the join to a left join, and put the filter within the on.

Note: You should use table aliases, they make the query easier to comprehend

select g.category, count(d.developerID) as TotalGames
from Game g
left join Developer d on d.developerID = g.developID and d.country = 'America'
group by g.category;

Do not make the mistake of putting the country filter in the where, it will not work.

Upvotes: 1

Related Questions