SQLite Case When clause not working correctly

I have a transactions table like this in SQLite:

id	date	transaction_type	volume	price	commission
65	1697846400	buy	100	32000	1600
66	1697846400	buy	100	2300	69
67	1697846400	buy	100	2300	69
68	1697846400	buy	100	23456	703
69	1697846400	sell	100	20000	600
70	1697846400	sell	100	20000	600
71	1697846400	sell	100	20000	1000
72	1697846400	sell	100	12000	300

I want to calculate the cashflow for each day which consists of in_flow and out_flow of the cash calculated based on transaction_type. The desired result is:

date	in_flow	out_flow
1697846400	xxxxxxxxx	xxxxxxxxx
1697500800	xxxxxxxxx	xxxxxxxxx

In which:

in_flow = Sum of all transaction value with transaction_type of 'sell' of the date
out_flow = Sum of all transaction value with transaction_type of 'buy' of the date

transaction value is calculate by transaction_type as:

buy: volume * price + commission
sell: volume * price - commission

And this is the query I have come up with so far:

SELECT t.date,
    CASE 
        When t.transaction_type = 'sell' THEN 
            sum(t.volume * t.price) - sum(t.commission)
    END as in_flow,
    Case
        When t.transaction_type = 'buy' Then 
            sum(t.volume * t.price) + sum(t.commission)
    End as out_flow
From 
    transactions as t
WHERE
    date = 1697846400
Group By t.date;

But it somehow only works for buy transaction:

date	in_flow	out_flow
1697846400	NULL	13210541

I tried to change the condition for sell transaction to LOWER(TRIM(t.transaction_type))='sell', the result remains the same.

Everything is fine when I query only the sell transactions:

Select date, sum(volume * price) - sum(commission) as in_flow
From transactions
Where transaction_type = 'sell' And date = 1697846400
Group By date;

date	in_flow
1697846400	7197500

Any idea of what I have done wrong?

I tried:

Changing the order of in_flow and out_flow in the query
Changing the condition for sell transaction to LOWER(TRIM(t.transaction_type))='sell'

Solution

Here is a way to do it using the aggregate function MAX() :

SELECT date,
       MAX(CASE WHEN transaction_type = 'sell' THEN total_price - commission END) AS in_flow,
       MAX(CASE WHEN transaction_type = 'buy' THEN total_price + commission END) AS out_flow
FROM (
  SELECT date, transaction_type, sum(commission) as commission, sum(volume * price) as total_price
  FROM 
    transactions
  GROUP BY date, transaction_type
) AS S
GROUP BY date;

Using your method, the CASE Clause must be inside the aggregate function SUM() not the opposite :

SELECT t.date,
    SUM(CASE 
        When t.transaction_type = 'sell' THEN 
            t.volume * t.price - commission
        END) in_flow,
    SUM(CASE 
        When t.transaction_type = 'buy' THEN 
            t.volume * t.price + commission 
        END) out_flow
From 
    transactions as t
Group By t.date;

Result :

date	in_flow	out_flow
1697846400	7197500	6008041

Demo here