CODEWITHSUNDEEP
mysql
Brob
Brob

Reputation: 675

MySQL vote calculation

I have an app which allows users to vote and have two tables

Video

|id|uri_code|filename|created_on|deleted|

Votes

|id|video_id|ip|created_on|

I'd like to be able to select all the video information and have the total number of votes for each video, is this possible with an sql statement or would I be best off doing it with the server side code?

Upvotes: 0

Views: 63

Answers (4)

Er. Anurag Jain
Er. Anurag Jain

Reputation: 1793

Hi You can use mysql query here

1.If you have to get only total votes for videos without any other conditions then

SELECT count( Votes.id ) AS total_vote, Video. *
FROM Video
LEFT JOIN Votes ON Video.id = Votes.video_id
WHERE Video.deleted = '1'
GROUP BY Video.id

2.If You have to get total votes for videos with some conditions for example deleted has 0 and 1 value 0 for non deleted video and 1 for deleted value of enum type and you have to get non deleted videos vote detail then 

SELECT count( Votes.id ) AS total_vote, Video. *
FROM Video
LEFT JOIN Votes ON Video.id = Votes.video_id
WHERE Video.deleted = '0'
GROUP BY video.id

Upvotes: 0

eggyal
eggyal

Reputation: 126055

It's certainly possible with SQL - this sort of thing is exactly what SQL is great at! You want to "group" a count of all votes by video, so the GROUP BY clause is exactly what you're after:

SELECT Video.*, COUNT(*)
FROM Video JOIN Votes ON Votes.video_id = Video.id
GROUP BY Video.id;

Upvotes: 2

Arion
Arion

Reputation: 31249

Maybe something like this:

SELECT
    Video.id,
    Video.uri_code,
    Video.filename,
    Video.created_on,
    Video.deleted,
    (
        SELECT
            COUNT(*)
        FROM
            Votes
        WHERE
            Votes.video_id=Video.id
    ) AS Total
FROM
    Video

Upvotes: 0

juergen d
juergen d

Reputation: 204924

select vi.*, count(vo.id) as votes
from video vi
inner join votes vo on vi.id = vo.video_id
group by vi.id

Upvotes: 0

Related Questions