CODEWITHSUNDEEP
mysqlsqldatabaseposts
a53-416
a53-416

Reputation: 3935

Tally votes using SQL and JOIN (is this possible?)

My question is similar to this one, which I've tried the solution to, but it wasn't exactly right for my scenario.

I have 2 tables: votes and posts. Here is a basic sketch: 

`posts`
----+------------------------------------------------------------------------+
| ID | post_title                                                            |
+----+-----------------------------------------------------------------------+
|  1 | Hello world.                                                          |
|  2 | This is a post!                                                       |
|  3 | What is the meaning of life?                                          |
|  4 | Looking for a good time?                                              |
+----+-----------------------------------------------------------------------

`votes`
+----+---------+
| ID | post_id | 
+----+---------+
|  1 |     1   |  
|  2 |     1   | 
|  3 |     1   |  
|  4 |     3   | 
|  5 |     3   |  
|  6 |     4   |  
+----+---------+

Problem:

I want to know how many votes each post got, and display them so that the post with the highest vote is at the top. 

     Post ID   Vote Count
   +---------+-----------+
   | 1       | 3         |
   | 3       | 2         |
   | 4       | 1         |
   | 2       | 0         |

What does the SQL query look like to achieve this goal?

Upvotes: 1

Views: 292

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52645

If you want to include a Posts with a Zero Vote count without doing a UINON you can do

  SELECT 
         p.id, 
         SUM(CASE WHEN v.post_id IS NOT NULL THEN 1 ELSE 0 END)  AS tally
  FROM  
      posts p
      LEFT JOIN votes v
      ON v.post_id = p.id
  ORDER BY  
      SUM(CASE WHEN v.postid IS NOT NULL THEN 1 ELSE 0 END) DESC
  GROUP 
    BY p.id

SUM/CASE is required here because COUNT(NULL) = 1

Since your structure is so close here's an example you can look at on data.SE

Upvotes: 0

StevieG
StevieG

Reputation: 8709

select post_id, count(*)
from votes
group by post_id
order by count(*) desc

EDIT:

select v.post_id, count(*)
from votes v INNER JOIN posts p ON v.post_id = p.id
group by v.post_id
order by count(*) desc

Upvotes: 5

onedaywhen
onedaywhen

Reputation: 57023

SELECT post_id, COUNT(*) AS tally
  FROM votes
 GROUP 
    BY post_id
UNION
SELECT ID AS post_id, 0 AS tally
  FROM posts
 WHERE ID NOT IN (SELECT post_id FROM votes);

Upvotes: 3

Related Questions