Vaia Vaia - 4 months ago 6
MySQL Question

Get both results with one query

I want to get results for likes by a specific user-id and all likes and dislikes of a specific page id.

My structure looks like this:


`pages`: (id, title)
`pages_likes`: (id, page_id, uid, status)


If the
status
is
-1
it's a dislike of a specific page, if it's
1
it's a like.

So, to get all likes this is my query:

SELECT COUNT(id) FROM pages_likes WHERE status = '1'


But now I also want to get if user-id 3 for example likes this page with

SELECT COUNT(id) FROM pages_likes WHERE status = '1' AND uid='3'


How can I achieve both in one query? I guess there has to be changed something right after the SELECT statement?

Answer

If you want to do this in a single query, use conditional aggregation:

SELECT SUM(CASE WHEN uid = '3' THEN 1 ELSE 0 END) AS threeLikes,
       SUM(CASE WHEN uid <> '3' THEN 1 ELSE 0 END) AS otherLikes
FROM pages_likes
WHERE status = '1'

Another option would be to use a UNION, cf. the answer given by @bernie

Update:

If you want page likes and dislikes in the same query, you can try:

SELECT SUM(CASE WHEN uid = '3'  AND status = '1' THEN 1 ELSE 0 END) AS threeLikes,
       SUM(CASE WHEN uid <> '3' AND status = '1' THEN 1 ELSE 0 END) AS otherLikes
       SUM(CASE WHEN uid = '3'  AND status = '0' THEN 1 ELSE 0 END) AS threeDislikes,
       SUM(CASE WHEN uid <> '3' AND status = '0' THEN 1 ELSE 0 END) AS otherDisikes
FROM pages_likes