Multiple conditional aggregates

Calculate multiple aggregates with different conditions in a single pass through the data using CASE expressions.

Problem​

You need to calculate various metrics from the same dataset with different conditions:

  • Count of buy orders
  • Count of sell orders
  • Average buy price
  • Average sell price
  • Total volume for large trades (> 1.0)
  • Total volume for small trades (≤ 1.0)

Running separate queries is inefficient.

Solution: CASE within aggregate functions​

Use CASE expressions inside aggregates to calculate all metrics in one query:

Multiple conditional aggregates in single queryDemo this query
SELECT
  symbol,
  count(CASE WHEN side = 'buy' THEN 1 END) as buy_count,
  count(CASE WHEN side = 'sell' THEN 1 END) as sell_count,
  avg(CASE WHEN side = 'buy' THEN price END) as avg_buy_price,
  avg(CASE WHEN side = 'sell' THEN price END) as avg_sell_price,
  sum(CASE WHEN amount > 1.0 THEN amount END) as large_trade_volume,
  sum(CASE WHEN amount <= 1.0 THEN amount END) as small_trade_volume,
  sum(amount) as total_volume
FROM trades
WHERE timestamp >= dateadd('d', -1, now())
  AND symbol IN ('BTC-USDT', 'ETH-USDT')
GROUP BY symbol;

Which returns:

symbolbuy_countsell_countavg_buy_priceavg_sell_pricelarge_trade_volumesmall_trade_volumetotal_volume
ETH-USDT2628702121633275.2866781298683273.6747631773655152042.0215079997451934.917160999976203976.93866900489
BTC-USDT78995971215294286.5212179358294304.921243218471713.12418872999938803.50576099972210516.629949730019

How it works​

CASE returns NULL for non-matching rows​

count(CASE WHEN side = 'buy' THEN 1 END)
  • When side = 'buy': CASE returns 1
  • When side != 'buy': CASE returns NULL (implicit ELSE NULL)
  • count() only counts non-NULL values
  • Result: counts only rows where side is 'buy'

Aggregate functions ignore NULL​

avg(CASE WHEN side = 'buy' THEN price END)
  • avg() calculates average of non-NULL values only
  • Only includes price when side is 'buy'
  • Automatically skips all other rows
Related Documentation