Clickhouse SQL select statement with sum of values based on condition

Chan Jing Hong 0
Viewed 50

I have a table in Clickhouse that stores all the user's transactions, as shown below.

Type is 'debit' or 'credit'

Timestamp is the time when the transaction is recorded.

┌─name────────────┬─type───
│ User            │ String │
│ Amount          │ String │
│ Type            │ String │
│ Category        │ String │
│ Timestamp       │ Number │
└─────────────────┴────────

I would like to find out the SUM of all the Amount of a specific Category for a user, between a specific Timestamp. If Type = 'debit', then add the amount, if Type = 'credit', subtract the amount.

So if I have the following data, this should be the expected result:

ROW 1
User: 1234
Amount: 10
Type: 'debit'
Category: 'family'

ROW 2
User: 1234
Amount: 3
Type: 'credit'
Category: 'family'


ROW 3
User: 1234
Amount: 3
Type: 'debit'
Category: 'work'

=== RESULT ===
Category: 'family'
Amount: 7

Category: 'work'
Amount: 3

So far I've only gotten through to selecting the transactions between a timestamp.

select User, Amount, Timestamp from transactions t
where t.User = '1234' AND t.Timestamp >= 1663166651 AND p.Timestamp <= 1663266651

I've also tried using the startsWith function to determine credit/debit

select User, sum(if(startsWith(Type, 'debit'), -Amount, Amount)) as Total, Timestamp from transactions t

but I got an error DB::Exception: Illegal type String of argument of function negate: While processing if(startsWith(Type 'debit')

clickhouse database sql
2 Answers

Probably something like:

SELECT User, sum(if(IsDebit, -Amount, Amount)) as Total
  from transactions t
 where t.User = '1234' 
   AND t.Timestamp >= 1663166651
   AND p.Timestamp <= 1663266651
 GROUP BY User
Geoff Genz0

This SQL as source data

select `User`,Amount,`Type`,Family from (
SELECT arrayJoin([
                    (1234,10,'debit','family'),
                    (1234,3,'credit','family'),
                    (1234,3,'debit','work')]) data
                    ,data.1 as `User`
                    ,data.2 as `Amount`
                    ,data.3 as `Type`
                    ,data.4 as `Family`
)

Result (same as your table):

User|Amount|Type  |Family|
----+------+------+------+
1234|    10|debit |family|
1234|     3|credit|family|
1234|     3|debit |work  |

Then,

SELECT Family,sum(total) as Total from(
    SELECT `Family`,if(`Type`='debit',Amount,-1*Amount) as total from ( 
        SELECT `User`,Amount,`Type`,Family from (
            SELECT arrayJoin([
                (1234,10,'debit','family'),
                (1234,3,'credit','family'),
                (1234,3,'debit','work')]) data
                ,data.1 as `User`
                ,data.2 as `Amount`
                ,data.3 as `Type`
                ,data.4 as `Family`
        )
    ) group by `Type`,Amount,Family
) group by Family

Results:

Family|Total|
------+-----+
work  |    3|
family|    7|

Using 'YourTable' as your table

select
    Family,
    sum(Total) as Total
from
    (
        SELECT
            `User`,
            sum(if(`Type` = 'debit', -Amount, Amount)) as Total
        from
            YourTable
        where 
            `User` = '1234'
            AND `Timestamp` >= 1663166651
            AND `Timestamp` <= 1663266651
        group by `User`
    ) group by `Family`
lino0
Related