SaTTko SaTTko - 2 months ago 9
MySQL Question

Select something from mysql database and order it by count (where)

I have a problem with selecting something from my database. Here is the sql sentence:

SELECT name
FROM table1
JOIN table2
ON table1.id=table2.advid
GROUP BY advid
ORDER BY COUNT(table2.likes) ASC


This will output
name
with the least
table2.likes
to the highest value of
table2.likes


The problem is that
table2.likes
contain both likes and dislikes. Likes are marked with 1, and dislikes are marked with 2 in the table.

Currently, if there is...

The table

...written in the table, the syntax will count both likes and dislikes so the result would be 6. I would need this result to be zero, which means when counting, dislikes have to be deduced from the number of likes. Which also means this part of the sentence:
ORDER BY COUNT(table2.likes) ASC
would have to be changed, but I don't know how.

Thanks for the help in advance.

Answer

Use conditional aggregation with SUM():

SELECT name 
FROM table1 t1 JOIN
     table2 t2
     ON t2.id = t2.advid 
GROUP BY name
ORDER BY SUM(CASE WHEN t2.likes = 1 THEN 1 ELSE -1 END) ASC;

Note: I changed the GROUP BY to be by name. The GROUP BY columns should match the columns you are selecting.

Comments