Filter a condition after aggregate function in SQL Server?

I’ve got database of flights and I want it to return average of delay, but I want it to filter ‘origin = ‘SFO” after counting average of arr_delay_new. If I use WHERE or HAVING it counts the average with condition origin = ‘SFO’. How to make it count avg first and filter later?

This one has no condition origin = ‘SFO’

SELECT A.airline_name, 
    AVG(F.arr_delay_new)
FROM Flight_delays F
JOIN Airlines A ON F.airline_id = A.airline_id
GROUP BY airline_name
ORDER BY AVG(arr_delay_new);

That’s a result of this, but there obviously are some lines with origin != ‘SFO’

Hawaiian Airlines Inc.: HA      4,20271940667491
Alaska Airlines Inc.: AS        7,45392753708848
Delta Air Lines Inc.: DL        12,2587876678628
Southwest Airlines Co.: WN      13,8239826054204
Virgin America: VX              13,9644670050761
SkyWest Airlines Inc.: OO       16,8082728044524
United Air Lines Inc.: UA       16,9504026008392
Spirit Air Lines: NK            18,0657512351061
American Airlines Inc.: AA      18,3753142444596
Frontier Airlines Inc.: F9      18,9802996961123
ExpressJet Airlines Inc.: EV    19,2545055411961
JetBlue Airways: B6             28,841148064491

If I use where or having:

SELECT airline_name, 
    AVG(arr_delay_new)
FROM Flight_delays F
JOIN Airlines A ON F.airline_id = A.airline_id
GROUP BY airline_name, origin
HAVING origin = 'SFO'
ORDER BY AVG(arr_delay_new); <code>

Result of the second has the condition origin = ‘SFO’, but the average is counted with this condition (I want average of all flights not only with origin = ‘SFO’)

Hawaiian Airlines Inc.: HA  4,87096774193548
Delta Air Lines Inc.: DL    9,6353591160221
United Air Lines Inc.: UA   13,8158534305478
Alaska Airlines Inc.: AS    14,4591194968553
Virgin America: VX          15,7448055407565
SkyWest Airlines Inc.: OO   17,5338904363974
Southwest Airlines Co.: WN  17,866981769075
American Airlines Inc.: AA  18,3386824324324
JetBlue Airways: B6         21,2571942446043
Frontier Airlines Inc.: F9  26

Answer

Use this HAVING clause:

HAVING COUNT(CASE WHEN origin = 'SFO' THEN 1 END) > 0

Advertisement

Answer