[SOLVED] Use GROUP BY with SELECT CASE WHEN statement

Use GROUP BY with SELECT CASE WHEN statement

I have a table like:

ORDER_DATE | ORDER_ID | ORDER_AMOUNT
2020-11-07 | 1        | 40
2020-11-07 | 1        | 60
2021-04-01 | 2        | 100

From 2020-07-01 to 2020-12-31 the sales tax was reduced from 19% to 16% due to Covid.

Now I need to know the gross amount per ORDER_ID. The result should be:

ORDER_ID | ORDER_AMOUNT
1        | 116
2        | 119

My code so far:

SELECT CASE WHEN ORDER_DATE BETWEEN '2020-07-01' AND '2020-12-31'
                THEN SUM(ORDER_AMOUNT) * 1.16
            WHEN (ORDER_DATE < '2020-07-01' OR ORDER_DATE > '2020-12-31')
                THEN SUM(ORDER_AMOUNT) * 1.19 
            ELSE 0
       END AS 'gross_amount'
--
FROM my_dwh_table
--
GROUP BY
        CASE WHEN ORDER_DATE BETWEEN '2020-07-01' AND '2020-12-31'
                THEN SUM(ORDER_AMOUNT) * 1.16
            WHEN (ORDER_DATE < '2020-07-01' OR ORDER_DATE > '2020-12-31')
                THEN SUM(ORDER_AMOUNT) * 1.19 
            ELSE 0
        END
;

but I get this error in DBeaver:

SQL Error [42000]: set function in group by clause

How can I use the CASE WHEN statement for calculating the different sales taxes with the GROUP BY on ORDER_ID? Where is the error in my code?

Solution

I think you want to phrase this with the CASE as an argument to the SUM():

SELECT ORDER_ID,
       SUM(CASE WHEN ORDER_DATE BETWEEN '2020-07-01' AND '2020-12-31'
                THEN ORDER_AMOUNT * 1.16
                ELSE ORDER_AMOUNT * 1.19 
           END) AS gross_amount
FROM my_dwh_table
GROUP BY ORDER_ID;

No ORDER BY is needed.

Note: This assumes that ORDER_DATE is never NULL (hence no need for an ELSE 0 and that '2020-07-01' is a valid date on your system. I would normally use DATE '2020-07-01' for a date constant.