MySql remove counted value from another count

Question

I'm trying to remove a counted value from another counted value but I can't get it to work.

I'm using MySql

This is my SQL:

SELECT `GameID`, 
count(case `Liked` when 1 then 1 else null end) as likes,
count(case `Liked` when 0 then 1 else null end) as dislikes
FROM `rating`
SET likes = likes + dislikes
GROUP BY `GameID` 
ORDER BY likes DESC

For each separate game I'm counting the amount of likes and dislikes this works fine. But I'm wondering if its possible to remove the amount of dislikes from the amount of likes like this:

SET likes = likes-dislikes

This doesn't seem to work.

Does anyone have any ideas how to do this?

Thanks for reading/helping!

Answer 1

There are multiple ways to achieve that

Repeat the expressions in the new column definition:

SELECT `GameID`, 
count(case `Liked` when 1 then 1 else null end) as likes,
count(case `Liked` when 0 then 1 else null end) as dislikes, 
count(case `Liked` when 1 then 1 else null end)-count(case `Liked` when 0 then 1 else null end) as diff
FROM `rating`
GROUP BY `GameID` 
ORDER BY diff DESC

Wrap it in another query:

select GameID, likes, dislikes, likes-dislikes AS diff
from (
    SELECT `GameID`, 
    count(case `Liked` when 1 then 1 else null end) as likes,
    count(case `Liked` when 0 then 1 else null end) as dislikes
    FROM `rating`
    SET likes = likes + dislikes
    GROUP BY `GameID` 
) as tmp
ORDER BY diff DESC