Return unique distinct value for group by dynamic fields based on value

Question

I have the following SQL query:

SELECT DISTINCT
    username,
    (CASE WHEN role_id = 1 THEN SUM(user_roles.hours) ELSE NULL END) AS `Admin`,
    (CASE WHEN role_id = 2 THEN SUM(user_roles.hours) ELSE NULL END) AS `Subscriber`
FROM user_roles
LEFT JOIN users ON users.id = user_roles.user_id;

The purpose of this query is to display on each rows the usernames and for each username the number of hours for Admins, Subscribers and so on.

This query produces the following results:

username    Admin    Subscriber
abc         10       0
abc         0        5
def         0        7
def         4        0

Each username should have a unique value that falls either under Admin or Subscriber (no users can have both)

I would like the output to be instead:

username    Admin    Subscriber
abc         10       5
def         4        7

Where there are no columns that show zeros and to have only one row result per usernames.

I hope it's clear what I'm trying to accomplish here.

Thanks for any help

Gordon Linoff · Answer 1 · 2020-05-21T21:04:52.987

This answers the original question you asked.

You want conditional aggregation. The "condition" is the argument to the aggregation function. But you can also simplify this to:

SELECT u.username,
       SUM(ur.role_id = 1) AS `Admin`,
       SUM(ur.role_id = 2) AS `Subscriber`
FROM user_roles ur LEFT JOIN
     users u
     ON u.id = ur.user_id
GROUP BY u.username, u.id;

This uses a MySQL shortcut where you can sum up a boolean value to get the count of "true"s. Also, I included the id in the GROUP BY in case users have the same name. Obviously it is not needed if you know that username is unique.

If you want to sum another column, then CASE expressions are needed:

SELECT u.username,
       SUM(CASE WHEN ur.role_id = 1 THEN ur.hours ELSE 0 END) AS `Admin`,
       SUM(CASE WHEN ur.role_id = 2 THEN ur.hours ELSE 0 END) AS `Subscriber`
FROM user_roles ur LEFT JOIN
     users u
     ON u.id = ur.user_id
GROUP BY u.username, u.id;

I edited the question, the CASE part is necessary because I'm actually summing the value of another column based on role_id — user765368, May 21 '20 at 19:34
@user765368 . . . You edited your question AFTER I answered, invalidating my answer. That is impolite, because it can attract downvotes. — Gordon Linoff, May 21 '20 at 21:05

forpas · Answer 2 · 2020-05-21T19:36:34.623

0

I think that you set the joins the other way around.
You should do a LEFT join of users to user_roles and use conditional aggregation:

SELECT 
    u.username,
    SUM(CASE WHEN r.role_id = 1 THEN r.hours ELSE 0 END) AS `Admin`,
    SUM(CASE WHEN r.role_id = 2 THEN r.hours ELSE 0 END) AS `Subscriber`
FROM users u LEFT JOIN user_roles r 
ON u.id = r.user_id
GROUP BY u.username

edited May 21 '20 at 19:36

answered May 21 '20 at 19:18

forpas

160,666
10
38
76

I edited the question, the CASE part is necessary because I'm actually summing the value of another column based on role_id – user765368 May 21 '20 at 19:33
See my edited answer. – forpas May 21 '20 at 19:36
Doesn't work. I'm still getting rows with zeros – user765368 May 21 '20 at 19:46
`GROUP BY u.username` returns only 1 row for each username by definition. If you get 2 rows then you are doing something different and you do not use this query as it is. – forpas May 21 '20 at 19:48
@user765368 if you are getting a row with 2 0s this means that the user does not have role_ids 1 and 2. Don't you want this user in the results? – forpas May 21 '20 at 20:02

Return unique distinct value for group by dynamic fields based on value

2 Answers2