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);

​x
 
SELECT A.airline_name,     AVG(F.arr_delay_new)FROM Flight_delays FJOIN Airlines A ON F.airline_id = A.airline_idGROUP BY airline_nameORDER 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

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

 
SELECT airline_name,     AVG(arr_delay_new)FROM Flight_delays FJOIN Airlines A ON F.airline_id = A.airline_idGROUP BY airline_name, originHAVING 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

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

Answer

Use this HAVING clause:

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

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

Advertisement

Answer