SQL count total

Question

+--------+-------+----------+-----------+
| Maker  | Model | SeatType | NoOfSeats |
+--------+-------+----------+-----------+
| Airbus | 340   | E        |       220 |
| Airbus | 340   | F        |        20 |
| Airbus | 380   | E        |       300 |
| Airbus | 380   | F        |        60 |
| Boeing | 747   | E        |       300 |
| Boeing | 747   | F        |        40 |
| Boeing | 777   | E        |       200 |
| Boeing | 777   | F        |        20 |
| Boeing | 787   | E        |       250 |
| Boeing | 787   | F        |        25 |
+--------+-------+----------+-----------+

Below is my attempt to create a query of this "PlaneSeats" table

select Maker, count(distinct Model)
from PlaneSeats 
group by Maker, Model 
having SUM(NoOfSeats) > 350;

and here is the result query

+--------+-----------------------+
| Maker  | count(distinct Model) |
+--------+-----------------------+
| Airbus |                     1 |
+--------+-----------------------+

but what I want to get is the Maker and total number of planes made by this maker, also for Makers who make any plane with total number of seats more than 350. I understand the count should be 2 here instead of 1 since there are 2 models for Maker Airbus. Please help me find out where it's wrong. Thank you.

you are getting count 1 because Maker Airbus with model "380" has 360 seats but Boeing Maker's any of model doesn't have seats more then 350. — Techno Crave
– Techno Crave, Commented Mar 4, 2017 at 5:53
@Poorva Yes I understand that part by looking at the table. Thanks ;) — Misuti
– Misuti, Commented Mar 5, 2017 at 3:27

Gordon Linoff · Accepted Answer · 2017-03-05 22:38:22Z

1

I think you need two levels of aggregation:

select maker, count(*)
from (select maker, model, sum(NoOfSeats) as numseats
      from planeseats ps
      group by maker, model
     ) mm
where numseats > 350
group by maker;

EDIT:

You want all planes to be counted for a make when one is big enough. That means turning the where to a having clause:

select maker, count(*)
from (select maker, model, sum(NoOfSeats) as numseats
      from planeseats ps
      group by maker, model
     ) mm
group by maker
having max(numseats) > 350;

edited Mar 5, 2017 at 22:38

answered Mar 4, 2017 at 2:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Misuti Over a year ago

Could you explain more on this 2 levels of aggregation, please? Also I tried to put the above code in and it turns out to be 1 instead of 2.

Strawberry Over a year ago

I think the OP wants the total number of models made by the manufacturer, regardless of the number of seats - as long as at least one model had in excess of 350 seats.

Collectives™ on Stack Overflow

SQL count total

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related