Javilico Javilico - 1 year ago 59
MySQL Question

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.

Answer Source

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

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.

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