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;

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