user2983865 user2983865 - 1 month ago 9
MySQL Question

count and group by two tables in mysql

I have two database tables and i want to order my posts by votes, i tried the query method below but it is not working as i wanted. my tables are like this :

posts
postID Title Desc
10
11
12

votes
votesid postvote value
1 10 0
2 10 1
3 10 1
4 12 1


I want query like this ( postvotes has PostID and when people vote it raising ) and if value is equal to 1 count number of postvote column.

I want this output

post 10 has 2 votes
post 11 has 0 votes
post 12 has 1 vote

I tried this code but not working

SELECT i.postID, i.Title, i.Desc, r.postvote, COUNT(r.postvote) AS mediaCnt
FROM posts i
LEFT JOIN votes r ON i.postID = r.postvote
GROUP BY i.postID
ORDER BY mediaCnt DESC


where i do mistake ?

Answer

This is what you wanted. See the query:

SELECT i.postID, i.Title, i.[Desc], COUNT(r.postvote) AS mediaCnt
FROM posts i LEFT JOIN votes r ON i.postID = r.postvote 
WHERE r.value = 1
GROUP BY i.postID, i.Title, i.[Desc], r.postvote
ORDER BY mediaCnt DESC  

Output:

count_output

Comments