CODEWITHSUNDEEP
mysqlcountleft-join
Anorionil
Anorionil

Reputation: 505

Mysql double left outer join with count

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?

Upvotes: 1

Views: 343

Answers (2)

juergen d
juergen d

Reputation: 204784

SELECT s.ID, 
       SUM(r.Accept = 1) as Accepts, 
       SUM(r.Accept = 0) as Declines 
FROM song s
LEFT OUTER JOIN review r ON s.ID = r.SongID
GROUP BY s.ID

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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.

Upvotes: 2

Related Questions