编辑Postgres 9.3
我正在编写一个包含以下架构的报告：http://sqlfiddle.com/#!15/fd104/2
当前查询工作正常,如下所示：
基本上它是一个3表内连接.我没有提出这个查询,但是离开它的开发人员我想修改查询.如您所见,TotalApplication只根据a.agent_id计算总应用程序.您可以在结果中看到totalapplication列.我想要的是删除它并将totalapplication更改为新的两列.我想添加一个完成调查和partitalsurvey专栏.所以基本上这部分将成为
@H_502_9@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具有相同的查询,唯一的区别是
@H_502_9@AND disposition = 'Partial Survey'和
@H_502_9@COUNT(a.id) as PartialSurvey @H_502_9@agent_id,name,completedsurvey,partialsurvey,loginhours,applicationperhour,rph一旦它没问题,那么applicationperhour和rph我可以自己修复它
如果我理解正确,您正在寻找过滤(条件)聚合：
@H_502_9@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语句：
