SQL GROUP BY but treat two values as one

Question

I have a transaction table like this

id | amount | transaction_date | status
---+--------+------------------+-------
 1 |   20   |   2021-01-01     |   1
 2 |   30   |   2021-01-01     |   2
 3 |   10   |   2021-01-01     |   3
 4 |   50   |   2021-01-01     |   1

I want to query the transactions to be grouped by transaction date and status. But for particular status, I want them to be counted or treated as 1 status. for example if I query the data like this

select status, sum(amount), transaction_date
from transactions
group by status, transaction_date

I will get this result

status | amount | transaction_date
-------+--------+-----------------
   1   |   70   |    2021-01-01   
   2   |   30   |    2021-01-01   
   3   |   10   |    2021-01-01   

my questions is how do I group status 2 and 3 as the same status, let's say as some alias 'complete', how do I get this result:

  status  | amount | transaction_date
----------+--------+-----------------
'pending' |   70   |    2021-01-01    
'complete'|   40   |    2021-01-01    

or is this impossible to query in Postgresql?

Answer 1

demo:db<>fiddle

SELECT
    CASE WHEN status = 1 THEN 'pending' ELSE 'complete' END as status,   -- 1
    SUM(amount) as amount,
    transaction_date
FROM transactions
GROUP BY 1, transaction_date                                             -- 2

1. You can use the CASE clause to create the pending status for 1 and complete for 2 and 3. Of course you need to adapt this to your actual requirements, maybe CASE WHEN status IN (2,3) THEN ... ELSE.. or add a second WHEN branch or whatever...
2. Then you can reference this CASE clause column in the GROUP BY