Sum column depending on values from another column on a single row (Pivot columns)

Community
,
1

I have a table with this schema

CREATE TABLE 'trades' (
  symbol SYMBOL capacity 256 CACHE,
  side SYMBOL capacity 256 CACHE,
  price DOUBLE,
  amount DOUBLE,
  timestamp TIMESTAMP
) timestamp (timestamp) PARTITION BY DAY WAL DEDUP UPSERT KEYS(symbol, timestamp);

And I have some data like this (the real data is at sub-second resolution, but for the example this should suffice)

INSERT INTO trades (symbol, side, price, amount, timestamp) VALUES
('BTC-USD', 'buy', 25757.235813, 0.020919, '2023-09-05T16:00:00.000000Z'),
('BTC-USD', 'sell', 25776.646252, 0.069064, '2023-09-05T16:15:00.000000Z'),
('BTC-USD', 'sell', 25791.132914, 0.048749, '2023-09-05T16:30:00.000000Z'),
('BTC-USD', 'buy', 25760.595216, 0.024248, '2023-09-05T16:45:00.000000Z');

I know I can get the total amount for the sells and the buys per day by doing this:

SELECT 
    timestamp, symbol, side, sum(amount) as sell    
FROM trades
SAMPLE by 1d;

But this gives me two rows, one for the ‘sell’ side and one for the ‘buy’ side. Any ideas to show this in a single row?

image

2

The trick is to pivot the rows into columns by using conditional statements. In QuestDB we can use the Case keyword for this, as in

SELECT 
    timestamp, symbol, 
    sum(case when side = 'sell' then amount else 0 end) as sell,
    sum(case when side = 'buy' then amount else 0 end) as buy
FROM trades
SAMPLE by 1d;

This will sum all the values from rows with side equal ‘sell’ in a column we name sell and the same with all the rows with value ‘buy’.

image