If values in Column A are the same and other values are equal to, then put X in another column

I have something like this…

FlightNum Class Seats SoldOut
10 A 0
10 B 5
10 C 15
10 D 25

I want to write a statement where FlightNum is the same, and A class has 0 seats, all rows for that FlightNum should put a "1" in the SoldOut column.

FlightNum Class Seats SoldOut
10 A 0 1
10 B 5 1
10 C 15 1
10 D 25 1

I’m not really sure where to start other than case statements, so I’m looking for some guidance.

>Solution :

We check if Class = 'A' and Seats = 0 and then we use count window function separated by FlightNum to give the result to the entire flight.

select  *
       ,count(case when Class = 'A' and Seats = 0 then 1 end) over(partition by FlightNum) as SoldOut
from    t
FlightNum Class Seats SoldOut
10 A 0 1
10 B 5 1
10 C 15 1
10 D 25 1

Fiddle

Leave a Reply