Using FILTER with COUNT(*)

Community
1

Hello,
Is there a way to do conditional COUNT() operations? I would need to count rows, but also apply conditions to them.

This is a simple example that works in Postgres. Is there something similar to it in QuestDB?

select
  count(1), -- Count all users
  count(1) filter (where gender = 'male'), -- Count male users
  count(1) filter (where adult is true) -- Count adult users
from users
2

You can use CASE:

SELECT count(CASE WHEN gender = 'male' THEN 1 END) FROM users

Or similar.

1 Like
3

Thank you. I should have tried it, it’s an older syntax

4

No problem, if we don’t have an open issue for filter syntax already, feel free to open one!

5

Made one here: add `FILTER` SQL:2003 syntax · Issue #5335 · questdb/questdb · GitHub

1 Like