SQL count occurrence of value by distinct id

Question

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?

Vamsi Prabhala · Accepted Answer · 2016-05-24 18:17:50Z

3

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

answered May 24, 2016 at 18:17

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DarkLeafyGreen Over a year ago

Thank you. Do you see a way without using "case when .. then .. else .. end"?

Paul L · Accepted Answer · 2016-05-24 19:32:30Z

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

Collectives™ on Stack Overflow

SQL count occurrence of value by distinct id

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related