There are 2 tables

users, likes

I make a request

 SELECT u.role, COUNT( u.id ), COUNT( l.id ) FROM users u LEFT JOIN ( SELECT user_id, id FROM likes GROUP BY user_id ) AS l ON u.id = l.user_id GROUP BY u.role ORDER BY COUNT( u.id ) DESC

I get the result format

 | role | COUNT( u.id ) | COUNT( l.id ) | |--------------------------------------| | 1 | 50 | 10 | |--------------------------------------| | 2 | 40 | 1 |

where the number of l.id is not calculated correctly

i.e., you need to get the number of users by roles and the number of likes submitted by users related to this role.

  • SELECT user_id, id FROM likes GROUP BY user_id is an invalid query. Either the id should be in the aggregation function, or there should not be a GROUP BY . Think about what you wanted to achieve with this subquery. - Regent
  • Count the quantity directly in the subquery and sum up in the external query - Mike
  • And the structure of the tables show? - Akina
  • @Regent thanks for the hint, I'll try to fix - mmNick
  • @mmNick to health. In principle, the answer you have already given. I just call for the fact that you need to evaluate what you write and what you want to achieve. - Regent

2 answers 2

Option with users who have no records in the likes table:

 SELECT u.role, COUNT(DISTINCT u.id) users, COUNT(l.id) likes FROM users u LEFT JOIN likes l ON u.id = l.user_id GROUP BY u.role

An example in SQLFiddle .

     SELECT u.role, COUNT(DISTINCT u.id) users, COUNT(DISTINCT l.id) likes FROM users u, likes l WHERE u.id = l.user_id GROUP BY u.role

    ?

    • the same values ​​in two columns and not true, differ much - mmNick
    • Sorry ... corrected. - Akina
    • I think the second COUNT DISTINCT superfluous. - Regent
    • one
      @mmNick if all users are to participate in the sample, regardless of whether they have likes or not, then instead of the current INNER/CROSS JOIN you must use LEFT JOIN : FROM users u LEFT JOIN likes l ON u.id = l.user_id . And WHERE then need to remove. - Regent
    • one
      @mmNick to health. Remember to accept this answer when the LEFT JOIN appears in it. - Regent