adriano_effe adriano_effe - 4 months ago 6
SQL Question

COUNT(*) not included in result when 0 and troubles joining

I'm having troubles making a query that, given a table with posts and one with postsvotes where postsvotes contains the id of the post and the type of vote ('UP' or 'DOWN'), outputs the number of 'UP' votes and 'DOWN' votes for each post.

I tried the following query using views:

DROP VIEW P, U, D;
CREATE VIEW P AS SELECT p.id, p.title, p.content FROM posts as p, users as u WHERE u.id = p.userId GROUP BY p.id ORDER BY p.datetime DESC;
CREATE VIEW U AS SELECT count(*) as Uvotes, postId FROM postsvotes, posts WHERE posts.id = postsvotes.postId AND postsvotes.type = 'UP' GROUP BY postId;
CREATE VIEW D AS SELECT count(*) as Dvotes, postId FROM postsvotes, posts WHERE posts.id = postsvotes.postId AND postsvotes.type = 'DOWN' GROUP BY postId;
SELECT Uvotes, Dvotes, u.postId as postId FROM U, D WHERE u.postId = d.postId GROUP BY postId;


It kinda works but only when there is at least 1 'UP' vote and 1 'DOWN' vote for the post, otherwise it doesn't consider it. I figured out the problem is in the U and D views, where the records with 0 votes are not put in the view.

Any idea on how to solve?

vkp vkp
Answer

Assuming the postvotes table only contains posts where there is atleast one upvote or a downvote, you can try the query below.

--Get upvote and downvote count for posts in the postvotes table
SELECT 
 postId
,count(case when type = 'UP' then 1 end) upvotes
,count(case when type = 'DOWN' then 1 end) downvotes
FROM postvotes 
GROUP BY postId
UNION ALL
--Get posts that have no votes at all
SELECT Id, 0, 0 
FROM posts p
WHERE NOT EXISTS (select 1 from postvotes where postId = p.Id)