Mike Mike - 2 years ago 78
SQL Question

Select sum of zero if no records in second table?

I did some research and learned about the

COALESCE(sum(num), 0)
function. The issue is the example I found only related to using one table.

I am calculating a sum from a second table, and if there are no records for an item in the second table, I still want it to show up in my query and have a sum of zero.

SELECT note.user, note.product, note.noteID, note.note, COALESCE(sum(noteTable.Score), 0) as points
FROM note, noteTable
WHERE note.user <> 3 AND note.noteID = noteTable.noteID

I am only recieving results if there is an entry in the second table noteTable. If there are scores added for a note, I still want them to show up in the result with a points value of zero.

Table Examples:


user | product | noteID |note
3 1 1 Great
3 2 2 Awesome
4 1 3 Sweet


noteID | score
1 5

The query should show me this:

user | noteID | sum(points)
3 1 5
3 2 0
4 3 0

But I am only getting this:

user | noteID | sum(points)
3 1 5

Answer Source


  note.noteID, note.note, 
  COALESCE(sum(noteTable.Score),0) as points 
FROM note
LEFT JOIN noteTable
ON note.noteID = noteTable.noteID
WHERE note.user <> 3 

and I guess you should add:

GROUP BY note.noteid

if you expect to get SUM for every user. So you want to get more then 1 record back.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download