I have this query

SELECT
  user_id,
  sum(case when event = 'withdraw' then value else 0 end) AS withdraw_value,
  sum(case when event = 'deposit' then value else 0 end) AS deposit_value,
  string_distinct_agg(event, ',') AS event
FROM events
WHERE timestamps > '2025-03-01 00:00:00'
  AND withdraw_value > 0
  AND event IN ('withdraw', 'deposit')
GROUP BY user_id

But it errors with Invalid column: withdraw_value. I understand this is because I am trying to use a calculated aggregated value. In other databases I could do

SELECT
  user_id,
  sum(case when event = 'withdraw' then value else 0 end) AS withdraw_value,
  sum(case when event = 'deposit' then value else 0 end) AS deposit_value,
  string_distinct_agg(event, ',') AS event
FROM events
WHERE timestamps > '2025-03-01 00:00:00'
  AND event IN ('withdraw', 'deposit')
GROUP BY user_id
HAVING withdraw_value > 0

But this errors with unexpected token [HAVING].

I have this query

SELECT
  user_id,
  sum(case when event = 'withdraw' then value else 0 end) AS withdraw_value,
  sum(case when event = 'deposit' then value else 0 end) AS deposit_value,
  string_distinct_agg(event, ',') AS event
FROM events
WHERE timestamps > '2025-03-01 00:00:00'
  AND withdraw_value > 0
  AND event IN ('withdraw', 'deposit')
GROUP BY user_id

But it errors with Invalid column: withdraw_value. I understand this is because I am trying to use a calculated aggregated value. In other databases I could do

SELECT
  user_id,
  sum(case when event = 'withdraw' then value else 0 end) AS withdraw_value,
  sum(case when event = 'deposit' then value else 0 end) AS deposit_value,
  string_distinct_agg(event, ',') AS event
FROM events
WHERE timestamps > '2025-03-01 00:00:00'
  AND event IN ('withdraw', 'deposit')
GROUP BY user_id
HAVING withdraw_value > 0

But this errors with unexpected token [HAVING].

• database
• time-series
• questdb

Share Improve this question Follow asked Mar 25 at 16:33 Javier RamirezJavier Ramirez 4,08511 gold badge2727 silver badges3636 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

We can wrap this on a subquery, and use a regular WHERE. This is the most efficient way to do a HAVING on QuestDB.

WITH event_values AS (
SELECT
  user_id,
  sum(case when event = 'withdraw' then value else 0 end) AS withdraw_value,
  sum(case when event = 'deposit' then value else 0 end) AS deposit_value,
  string_distinct_agg(event, ',') AS event
FROM events
WHERE timestamps > '2025-03-01 00:00:00'  
  AND event IN ('withdraw', 'deposit')
GROUP BY user_id
)
SELECT * from event_values
WHERE withdraw_value > 0
;