Number of hits and preferences in the image
The table has columns img_id
and likes
. Example:
1 1
1 1
1 0
2 0
2 0
3 1
so when running these queries:
select img_id, count(likes)
from my_table
where likes = 1
group by img_id
here is the result:
1 2
3 1
and I run:
select img_id, count(likes)
from my_table
where likes = 0
group by img_id
result:
1 1
2 2
my question is:
how can i get back to query1 img_id 2 = 0 i mean img_id = 2 dislikes also in query 2 img_id = 3 has no dislikes so i need to return 3 -> 0
+3
source to share
1 answer
In MySQL and SQL Server
SELECT img_id,
SUM(CASE WHEN likes = 1 THEN 1 ELSE 0 END) totalLikes,
SUM(CASE WHEN likes = 0 THEN 1 ELSE 0 END) totalDisLikes
FROM tableName
GROUP BY img_ID
or just in MySQL,
SELECT img_id,
SUM(likes = 1) totalLikes,
SUM(likes = 0) totalDisLikes
FROM tableName
GROUP BY img_ID
+9
source to share