I have the following DB structure:
I want to view all songs and count how many accepts and how many declines a song has.
I have the following query:
SELECT s.*, COUNT(ra.ID) as Accepts, COUNT(rd.ID) as Declines
FROM song s
LEFT OUTER JOIN review ra ON s.ID = ra.SongID AND ra.Accept = 1
LEFT OUTER JOIN review rd ON s.ID = rd.SongID AND rd.Accept = 0
GROUP BY s.ID
This doesn't yield the correct result. I have a song with 3 accepts and 1 decline, the query outputs 3 accepts and 3 declines. How do I fix this?
The issue with juergen's solution is that if a song has no review then r.accept will be null. When you compare a null value using = you get null as a result. (You should use IS in order to compare null values). Then, if you SUM (add) null values then you get a null value again.
So, you should make sure that when a song has no review, it will return 0 instead of null:
SELECT s.ID,
COALESCE(SUM(r.Accept = 1), 0) as Accepts,
COALESCE(SUM(r.Accept = 0), 0) as Declines
FROM song s
LEFT OUTER JOIN review r ON s.ID = r.SongID
GROUP BY s.ID
COALESCE will take the first non-null argument from the list, so if the SUM is null then a 0 will be put in its place.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments