CODEWITHSUNDEEP
sqlfiltermultiple-conditions
Thyme
Thyme

Reputation: 29

SQL Filter two aggregate functions with different conditions

I want to get the difference between 

AVG(arr_delay_new) 
WHERE fl_date BETWEEN '2017/07/24' AND '2017/07/31'

and 

AVG(arr_delay)_new 
WHERE fl_date BETWEEN '2017/07/01' AND '2017/07/23'

This one of course returns 0 and has no conditions with dates.

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

Upvotes: 0

Views: 944

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can use conditional aggregation:

SELECT airline_name,
       (AVG(CASE WHEN fl_date BETWEEN '2017-07-24' and '2017-07-31' THEN arr_delay_new END) -
        AVG(CASE WHEN fl_date BETWEEN '2017-07-01' and '2017-07-23' THEN arr_delay_new END)
       ) as AVG_DIFF
FROM Flight_delays F JOIN
     Airlines A
     ON A.airline_id = F.airline_id
GROUP BY airline_name;

This assumes that arr_delay_new has a type that can be averaged. Some databases are reluctant to do averages on date/times directly.

Upvotes: 2

Related Questions