38

EDIT Postgres 9.3

I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2

The current query is working fine which looks like this:

enter image description here

Basically it is a 3 table inner join. I did not make this query but the developer who left it and I want to modify the query. As you can see, TotalApplication just counts the total application based on the a.agent_id. And you can see the totalapplication column in the result. What I want is to remove that and change the totalapplication to a new two column. I want to add a completedsurvey and partitalsurvey column. So basically this part will become

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

I just added AND disposition = 'Completed Survey' But I need another column for partialsurvey which has the same query with completedsurvey being the only difference is

AND disposition = 'Partial Survey'

and

COUNT(a.id) as PartialSurvey

But I dunno where to put that query or how will be the query look like.So the final output has these columns

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

Once it is ok then applicationperhour and rph I can fix it myself

76

If I understand you correctly, you are looking for a filtered (conditional) aggregate:

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;

Edit
for older versions (< 9.4) you need to use a case statement:

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;
  • I'm using 9.3. Is it ok? – jackhammer013 Sep 1 '15 at 8:11
  • @JoeneFloresca: no, for 9.3 you need a CASE statement. You should always specify the version you are using, especially if it's not the current one. – a_horse_with_no_name Sep 1 '15 at 8:12
  • Thank so much! works perfect now. Sorry bout that, edited my post and will keep that in mind next time. Thanks :) – jackhammer013 Sep 1 '15 at 8:16
  • Link to docs on FILTER: postgresql.org/docs/current/static/… – bcattle Apr 9 '18 at 23:38
  • Useful for me (/) – Sajeev Jul 23 '18 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.