I've been trying to convert a CASE WHEN query in my SELECT statement into an aggregate so that I can avoid it in the GROUP BY statement. However, I cannot seem to do it properly. Eventually, I settled for a sub-query, which makes it slightly messier than I wished it would be. Would love to hear your advice and how I can improve the query to make it look neater!
ID | Task | Method | Worker_ID | Location | Duration
1 | Run | A | 111 | EUR | 15
2 | Run | A | 333 | EUR | 13
3 | Run | B | 111 | EUR | 10
4 | Run | A | 222 | USA | 12
5 | Walk | A | 111 | EUR | 9
Was hoping it will turn out like this:
Task | Method | Sum_Duration | Sum_Duration_EUR | Sum_Duration_USA
Run | A | 40 | 28 | 12
Run | B | 10 | 10 | null
Walk | A | 9 | 9 | null
I manage to do this with a subquery:
SELECT TASK, METHOD, SUM(SUM_DURATION) AS SUM_DURATION,
SUM(SUM_DURATION_EUR) AS SUM_DURATION_EUR,
SUM(SUM_DURATION_USA) AS SUM_DURATION_USA
FROM (SELECT TASK, METHOD, SUM(DURATION) AS SUM_DURATION,
CASE WHEN LOCATION = 'EUR' THEN SUM(DURATION) END AS SUM_DURATION_EUR,
CASE WHEN LOCATION = 'USA' THEN SUM(DURATION) END AS SUM_DURATION_USA
FROM EXAMPLE_TABLE
GROUP BY 1,2,LOCATION) T
GROUP BY 1,2
Is there a way I can do this without having to create a sub-query?
Big thanks in advance!
Use a filtered aggregation:
SELECT task, method,
sum(sum_duration) as sum_duration,
sum(sum_duration) filter (where location = 'EUR') as sum_duration_eur,
sum(sum_duration) filter (where location = 'USA') as sum_duration_usa
FROM example_table
GROUP BY task, method