SQL Sorting users in the database by the highest number of received opinions-CodePudding

I have two tables:

`users` (id, phone_number, first_name, last_name, email)
`opinions` (id, user_id, type, comment, date)

In the second table opinions field type may have value 'positive' or 'negative'. Users may receive multiple positive and negative opinions. I want to construct sql query which allow me to sort users by highest number of positive or highest number of negative opinions.

I've created sql query like below but it seems that it doesnt work and it's merging total number of opinions inside one record but i want to display sorted table of all users by highest number of positive or negative opinions. Unfortunally my example doesnt work and i cant figure out how to do that. Could you please help me?

SELECT
    users.*,
    count(opinions.id) as sum_negative
FROM users
    LEFT JOIN opinions ON opinions.user_id = users.id
WHERE
    opinions.type = 'negative'
ORDER BY sum_negative DESC

CodePudding user response：

You need to group results by user id https://www.w3schools.com/sql/sql_groupby.asp

SELECT
    users.*,
    count(opinions.id) as sum_negative
FROM users
    LEFT JOIN opinions ON opinions.user_id = users.id
WHERE
    opinions.type = 'negative'
GROUP BY users.id
ORDER BY sum_negative DESC