92

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

0

1 Answer 1

168

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;
2

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.