CODEWITHSUNDEEP
mysqlsql
septemberbrain
septemberbrain

Reputation: 1008

Combining count from one table and distinct from another

I've setup a contest where video submissions are sent in and then people vote on them. Submissions are sent into a table submissions with this structure:

submission_id, title, videoname

The voting table votes structure is:

video_id, voter_id

The video_id correlates to the submission_id in the submissions table.

I want to get the number of votes for each video like so:

select video_id, count(1) from votes group by submission_id

But I also want to display the title for each video so the result would be:

video_id, count, title

I am a sql noob so please forgive me if this is a simple statement. I have done some research and was not able to come up with something on my own and would appreciate any help.

Upvotes: 1

Views: 51

Answers (4)

Cyril Gandon
Cyril Gandon

Reputation: 17048

You have to make a join for retrieve the votes and the video title, between the two tables like this :

SELECT submissions.submission_id
     , COUNT(1)
     , submissions.videoname
FROM submissions LEFT OUTER JOIN votes
    ON votes.video_id = submissions.submission_id
GROUP BY submissions.submission_id, submissions.videoname

Upvotes: 0

Matt Busche
Matt Busche

Reputation: 14333

This will return every video from you submissions table and show the number of votes in the votes table. If there have not been any votes it will show up in the results with a NULL as the Votes column

SELECT video_id, title, COUNT(voter_id) Votes
FROM submissions s
    LEFT OUTER JOIN votes v ON s.submission_id = v.video_id
GROUP BY video_id, title,

Upvotes: 0

Miguel Prz
Miguel Prz

Reputation: 13792

select s.submission_id, s.title, s.videoname, c.cnt
from
submissions s,
(select video_id, count(1) as cnt from votes group by video_id) c
where
s.submission_id = c.video_id

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

I would recommend doing a LEFT JOIN instead of an INNER JOIN... and COUNT(v.video_id) instead of COUNT(*). This way you will still return submissions that currently have 0 votes:

select
  s.submission_id as video_id,
  count(v.video_id) as vote_count,
  s.title
from
  submissions s
  left join votes v on v.video_id = s.submission_id
group by 
  s.submission_id, 
  s.title

Upvotes: 2

Related Questions