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







All Articles