Postgres Count 在同一個查詢中具有不同的條件

編輯Postgres 9.3

我正在編寫一份具有以下架構的報告：http ://sqlfiddle.com/#!15/fd104/2

目前查詢工作正常，如下所示：

基本上它是一個 3 表內連接。我沒有進行此查詢，而是留下它的開發人員，我想修改查詢。如您所見，TotalApplication僅根據a.agent_id. 您可以在結果中看到該totalapplication列。我想要的是刪除它並將其更改totalapplication為新的兩列。我想添加一個completedsurvey和partitalsurvey列。所以基本上這部分會變成

SELECT a.agent_id as agent_id, COUNT(a.id) as CompletedSurvey
FROM forms a WHERE  a.created_at >= '2015-08-01' AND 
a.created_at <= '2015-08-31' AND disposition = 'Completed Survey'
GROUP BY a.agent_id

我剛剛添加AND disposition = 'Completed Survey'但我需要另一列partialsurvey具有相同查詢completedsurvey的唯一區別是

AND disposition = 'Partial Survey'

和

COUNT(a.id) as PartialSurvey

但我不知道將該查詢放在哪里或查詢的外觀如何。所以最終輸出有這些列

agent_id, name, completedsurvey, partialsurvey, loginhours, applicationperhour, rph

一旦沒問題，那麼 applicationperhour 和 rph 我可以自己修復它

如果我理解正確，您正在尋找過濾（條件）聚合：

SELECT a.agent_id as agent_id, 
      COUNT(a.id) filter (where disposition = 'Completed Survey') as CompletedSurvey, 
      count(a.id) filter (where disposition = 'Partial Survey') as partial_survey
FROM forms a 
WHERE a.created_at >= '2015-08-01' 
 AND a.created_at <= '2015-08-31' 
GROUP BY a.agent_id;

編輯

舊版本（< 9.4）你需要使用一個case語句：

SELECT a.agent_id as agent_id, 
      COUNT(case when disposition = 'Completed Survey' then a.id end) as CompletedSurvey, 
      COUNT(case when disposition = 'Partial Survey' then a.id end) as partial_survey
FROM forms a 
WHERE a.created_at >= '2015-08-01' 
 AND a.created_at <= '2015-08-31' 
GROUP BY a.agent_id;

引用自：https://dba.stackexchange.com/questions/112796