MuriloKunze MuriloKunze - 1 year ago 47
SQL Question

How to sum using where or case in a group by?

I have this result set from a group by expression:

enter image description here

This is from:

SELECT
list.Id,
SUM(answer.score) totalScoreOfList
--,sum(answer.score) score of student id 5.. something like sum(answer.score) where studentId = 5
-- or case when studenti.id = 5 then sum(answer.score) else 0 end
FROM
list
LEFT JOIN student ...
LEFT JOIN answer ...
GROUP BY
exercise.id


What technique can I use to get the result above?

enter image description here

Answer Source

As your question title says, you need to use a CASE expression:

SELECT
    list.Id, 
    SUM(answer.score) totalScoreOfList,
    SUM(CASE WHEN studentId = 5 THEN answer.score END) [score of student id 5]
FROM
    list
LEFT JOIN student ...
LEFT JOIN answer ...
GROUP BY 
    list.id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download