SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t
    (FlightNum int, Class varchar(1), Seats int)
;
    
INSERT INTO t
    (FlightNum, Class, Seats)
VALUES
    (10, 'A', 0),
    (10, 'B', 5),
    (10, 'C', 15),
    (10, 'D', 25)
;
4 rows affected

select * from t;


FlightNum
Class
Seats


10
A
0

10
B
5

10
C
15

10
D
25


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


Warning: Null value is eliminated by an aggregate or other SET operation.