Build postgresql query based on multiple conditions

Question

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?

Indeed, many thanks, i never thought by changing the message code with the id @then would make it. You may want to add an answer will accept it. Thanks. — Daniel Treica
– Daniel Treica, Commented Mar 16, 2016 at 7:42

Ihor Romanchenko · Accepted Answer · 2016-03-16 09:55:19Z

1

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')

answered Mar 16, 2016 at 9:55

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Build postgresql query based on multiple conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related