CODEWITHSUNDEEP
sql
DarkLeafyGreen
DarkLeafyGreen

Reputation: 70406

SQL count occurrence of value by distinct id

Given following table A

FlightID| Roles
1       | Pilot
1       | Steward
1       | Steward
2       | Pilot
2       | Co-Pilot

How can I determine the number of stewards on each distinct flight? The output should be like this:

FlightID| Count
1       | 2
2       | 0

First I tried:

select FlightID, count(Role) from A group by FlightID

but this gives me the total number of roles per flight. Then I tried:

select FlightID, count(Role) from A where Role="Steward" group by FlightID

this is partially right since it gives me the number of stewards per flight, but it does not take into account 0 stewards. How can I also include 0 stewards into the result?

Upvotes: 0

Views: 37

Answers (2)

Paul L
Paul L

Reputation: 938

Does your table really not have a primary key on it? If it does, this is a fairly easy LEFT JOIN:

SELECT a1.FlightId, COUNT(a2.FlightId) 
FROM A a1 LEFT JOIN A a2 ON a1.id = a2.id
AND a2.Roles = 'Steward'
GROUP BY a1.FlightId;

http://ideone.com/BySBSx

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use conditional aggregation.

select FlightID
,sum(case when Role = 'Steward' then 1 else 0 end) 
--or count(case when Role = 'Steward' then 1 end)
from A 
group by FlightID

Upvotes: 3

Related Questions