CODEWITHSUNDEEP
sqlpostgresqlgroup-bycase
Jason
Jason

Reputation: 13

multiple case results with a group by

I don't even know what I am technically asking for so it has been rough trying to find an answer. I have this query working in MySQL but switching over to PostgreSQL has been a little challenging.

SELECT call.call_id,
    SUM(CASE WHEN indicator.indicator_name = 'Appointment Set' THEN indicator_score.score_value  ELSE '0' END) as "Appointment Set" ,
    SUM(CASE WHEN indicator.indicator_name = 'Ask for Business' THEN indicator_score.score_value  ELSE '0'  END) as "Ask for Business"
FROM call
JOIN indicator_score ON indicator_score.call_id = call.call_id
JOIN indicator ON indicator.indicator_id = indicator_score.indicator_id
JOIN org_unit ON org_unit.org_unit_id = call.org_unit_id
WHERE call.org_unit_id IN (4147, 4153)
group by call.call_id, indicator_name

Each call_id can and should have a score for both "Ask for Business" and "Appointment Set", but what I am getting is one with a score and the other with a '0'.

call_id   | Appointment Set | Ask for Business
--------------------------------------------
3,001,144 |         0       |         89
3,001,145 |       100       |          0

What I am looking for is something like this:

call_id   | Appointment Set | Ask for Business
--------------------------------------------
3,001,144 |      100        |         89

I have also tried this structure, but I am getting the same results.

SELECT call.call_id,
CASE indicator.indicator_name WHEN 'Appointment Set' THEN sum(indicator_score.score_value)  ELSE '0'  END as Appointment_Set ,
CASE indicator.indicator_name WHEN 'Ask for Business' THEN sum(indicator_score.score_value)  ELSE '0'  END as Ask_for_Business
FROM call
JOIN indicator_score ON indicator_score.call_id = call.call_id
JOIN indicator ON indicator.indicator_id = indicator_score.indicator_id
JOIN org_unit ON org_unit.org_unit_id = call.org_unit_id
WHERE call.org_unit_id IN (4147, 4153)
group by call.call_id, indicator_name

Upvotes: 1

Views: 46

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

With

group by call.call_id, indicator_name

you get for one call_id of course two rows, one for indicator_name = 'Appointment Set', one for indicator_name = 'Ask for Business'.

If you want one row per call_id only, then only group by call_id:

group by call.call_id

Upvotes: 3

Related Questions