March 6, 2012
Combining two columns on a id
Question by Snowflow
this is my database
What i want to do, is, for a given userid, show the total times he has been reported.
I have read various other questions on the matter, but I’m still stumped.
The latest query i tried was
select
sum(posts.timesreported + comments.timesreported) AS total_reports
FROM
posts
INNER JOIN comments ON (posts.userid = comments.userid)
WHERE posts.userid=5 AND comments.userid=5;
But this must be wrong as the number i get is much too high
Thanks!
Answer by SOaddict
SELECT
CASE WHEN NULL
THEN 0
ELSE (select sum(posts.timesreported) AS total_posts_reports
FROM posts INNER JOIN users ON (posts.userid = users.id)
WHERE posts.userid=5)
END
+
CASE WHEN NULL
THEN 0
ELSE (select sum(comments.timesreported) AS total_comments_reports
FROM comments INNER JOIN users ON (comments.userid = users.id)
WHERE comments.userid=5)
END
FROM DUAL;
Answer by Starx
WHERE posts.userid=5 AND comments.userid=5; is unnecessary since the tables are joined.
And sum operator is not correct logically
Use this query
select
sum(posts.timesreported) + sum(comments.timesreported) AS total_reports
FROM
posts
INNER JOIN comments ON (posts.userid = comments.userid)
WHERE posts.userid=5
