CODEWITHSUNDEEP
mysqljoin
Javilico
Javilico

Reputation: 13

mysql count with right join return some wrong values

I have a problem with count in a right join, with this code i can count how many visites have a video

SELECT video_id, COUNT(video_id) AS Views FROM fm_views GROUP BY video_id ORDER BY Views DESC;

It returns the following example.

video_id    Views
1668306      10
21041317      4
3845          2
13796095      1
16808537      1
11170454      1

This is right count, now i put the example 2 that is the wrong count

SELECT fm_video.*, IFNULL(COUNT(fm_views.video_id), 0) AS Views FROM fm_views RIGHT JOIN fm_video ON fm_video.video_id = fm_views.video_id GROUP BY fm_video.video_id ORDER BY Views DESC, id DESC;

This is the problem, now i get that (Simplified version without zero values)

video_id    Views
1668306      10
21041317      4
3845          >>4<< This is the problem
13796095      1
16808537      1
11170454      1

Now i get 4 Views in video 3845 when i have only 2 entries with that video_id after the right join, the rest of values are OK, but i think that when i have more entries in the database, i will get more problems with this wrong values

I cant find any helpful with this simple query like format.

Upvotes: 1

Views: 34

Answers (2)

Ahmad Musa
Ahmad Musa

Reputation: 78

Actually if you select * you will see some empty fm_views rows they are being counted somehow, add the WHERE filter, and rewrite your query as follows:

SELECT fm_video.*, IFNULL(COUNT(fm_views.video_id), 0) AS Views 
FROM fm_views 
RIGHT JOIN 
fm_video ON fm_video.video_id = fm_views.video_id 
WHERE fm_views.video_id is not null
GROUP BY fm_video.video_id ORDER BY Views DESC, id DESC;

you just need to filter out the null rows 

WHERE fm_views.video_id is not null

This happens if the joined tables have different row numbers that's why you have the correct count on 2 rows

** please vote up and mark as accepted answer if you find it helpful **

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

I find right join quite hard to follow. left join seems more natural because it keeps all rows in the first table. Also, COUNT() cannot return NULL. So, this is the query:

SELECT vd.*, COUNT(vw.video_id) AS Views
FROM fm_video vd LEFT JOIN
     fm_views vw
     ON vd.video_id = vw.video_id
GROUP BY vd.video_id
ORDER BY Views DESC, id DESC;

If this is returning unexpected results, then it suggests that you have bad data, or your expectation is wrong.

For the first possibility, I would suggest validating that fm_video(video_id) is the primary key on the table (or at least unique). You can also run this code to see if there are duplicates:

select video_id
from fm_video
group by video_id
having count(*) > 1;

You should also check that video_id has the same type in both tables.

Upvotes: 1

Related Questions