CODEWITHSUNDEEP
sqlpostgresql
Daniel Treica
Daniel Treica

Reputation: 85

Build postgresql query based on multiple conditions

I have this query which selects the count of each "messagecode" based on a defined time interval.

SELECT coalesce(count(case when messagecode = 'F-100' then 'F-100' end), 0) as fixed,
       coalesce(count(case when messagecode = 'H-100' then 'H-100' end), 0) as hope,
       coalesce(count(case when messagecode = 'G-100' then 'G-100' end), 0) as good, 
       coalesce(count(case when messagecode = 'T-100' then 'T-100' end), 0) as todo
       FROM messages WHERE messagetime >= current_timestamp - ('5' * interval '1 minute')

Output for entries

fixed: 115,
hope: 334,
good: 1045,
todo: 6000

I want to make something similar but to select the amount of unique ID's are having this messagecode, I know I should start by doing 

SELECT coalesce(count(DISTINCT id), 0) ...

But my issue is, I don't know yet how to do this for multiple conditions on the same query. Expected output :

fixed: 13, (13 unique ids exist)
hope: 45, (45 unique ids exist)
good: 110, (110 unique ids exist)
todo: 414 (414 unique ids exist)

How will I be able to achieve it?

Upvotes: 0

Views: 185

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28531

The query may look like:

SELECT coalesce(count(distinct case when messagecode = 'F-100' then id end), 0) as fixed,
       coalesce(count(distinct case when messagecode = 'H-100' then id end), 0) as hope,
       coalesce(count(distinct case when messagecode = 'G-100' then id end), 0) as good, 
       coalesce(count(distinct case when messagecode = 'T-100' then id end), 0) as todo
       FROM messages WHERE messagetime >= current_timestamp - ('5' * interval '1 minute')

Upvotes: 1

Related Questions