根据多个条件构建postgresql查询(Build postgresql query based on multiple conditions)

 我有这个查询，它根据定义的时间间隔选择每个“消息代码”的计数。 
 
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')
 
 输入条目 
 
fixed: 115,
hope: 334,
good: 1045,
todo: 6000
 
 我想做类似的事情但是要选择具有此消息代码的唯一ID的数量，我知道我应该从做起 
 
SELECT coalesce(count(DISTINCT id), 0) ... 
 
 但我的问题是，我还不知道如何在同一查询的多个条件下执行此操作。 预期产量： 
 
fixed: 13, (13 unique ids exist)
hope: 45, (45 unique ids exist)
good: 110, (110 unique ids exist)
todo: 414 (414 unique ids exist)
 
 我怎样才能实现它？ 

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?

原文：https://stackoverflow.com/questions/36016533