Mysql: Group by with condition

Question

I've searched on my question but I couldn't really find what I was looking for or maybe I just didn't understand the examples. If there is a similar post please point me to the right thread.

What I'm trying to do is the following: I have results like the table below which I generated with a very simple query:

SELECT id, first_name, last_name, email, roles, created 
FROM user

As you can see a user can have two roles: User or teacher. Some persons are only teacher and some are only user. However, some of them are both teacher and user. Now I want to group by e-mail adres, but of course this doesn't work on persons who are both user and teacher. I would like to group by e-mail and in case a person has both roles I want to keep the user role in the results. I understood this can be done with an if condition but I can't figure out where or how to do it.

+------+------------+-----------+-----------------------+--------------+
| id   | first_name | last_name | email                 | roles        |
+------+------------+-----------+-----------------------+--------------+
| 9798 | person     | one       | personOne@gmail.com   | ROLE_USER    |
| 9800 | person     | one       | personOne@gmail.com   | ROLE_TEACHER |
| 9801 | person     | two       | personTwo@gmail.com   | ROLE_TEACHER |
| 9802 | person     | three     | personThree@gmail.com | ROLE_TEACHER |
| 9803 | person     | four      | personFour@gmail.com  | ROLE_USER    |
+------+------------+-----------+-----------------------+--------------+

So my query should be something like this:

SELECT id, first_name, last_name, email, roles, created 
FROM user
group by email (if count(email) > 1 "ROLE_USER from roles should end up in results")

Could anybody point me in the right direction or make an example? Thanks so much!

Your `Group By` is not valid SQL. Please see: https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc — Madhur Bhaiya, Nov 02 '18 at 10:33
Please [edit](https://stackoverflow.com/posts/53116836/edit) your question to add expected output, based on given sample data. — Madhur Bhaiya, Nov 02 '18 at 10:34
Hi, welcome to SO. Please see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Nov 02 '18 at 11:09

score 0 · Answer 1 · answered Nov 02 '18 at 11:10

it is probably easier than you think, but like you said, being new, might not have understood. That said, and trying to interpret other commands not to your data scenario is a little harder. You know you have the post possible combinations of 3... User, Teacher or both. I would just add a column to represent each possible grouped by email. Now, being that you are grouping by email, do you still need the "ID", and "created" fields? I'm not sure, but we'll throw those in too just in case.

select
      u.email
      max( u.first_name ) first_name,
      max( u.last_name ) last_name,
      max( case when u.roles = 'ROLE_USER' then 1 else 0 end ) IsUserRole,
      max( case when u.roles = 'ROLE_TEACHER' then 1 else 0 end ) IsTeacherRole
   from
      user u
   group by
      u.email

By applying a max, it for the name, if you had a person whose name changed, or had a mis-entry into the system, you would just get one, but if names were the same, it does not matter. As for the User / Teacher roles, I am just returning a 1 if the record returns true, otherwise a zero. This SHOULD get you what you need.

Thanks for your suggestion! Although I get the right amount of results I would prefer a way to drop the teacher role in case a person has two roles. — F.Doe, Nov 02 '18 at 14:20
@F.Doe, you could always just do a COUNT(*) as NumberOfRoles and get either 1 or 2, but that would not differentiate if one was a specific type or the other. This way, you have an indicator on a single row that shows which is qualified for which. — DRapp, Nov 02 '18 at 20:39

score 0 · Answer 2 · answered Nov 02 '18 at 11:16

0

if i understand the question right something like this should help you

SELECT u.id,u.email, u.fname, u.llname, group_concat(r.role) FROM user u
 LEFT OUTER JOIN user r ON (u.email = r.email) GROUP BY u.email

answered Nov 02 '18 at 11:16

Moneer Kamal

1,837
16
25

Thanks for your suggestion. I tried your query and I think the group_concat function is a really creative way I'd never considered. Although the query gives the right results I would still prefer the teacher roles to be discarded when there's more than one role. – F.Doe Nov 02 '18 at 14:14

score 0 · Accepted Answer · answered Nov 02 '18 at 11:37

If there are only 2 roles you could

select * from t where roles = 'role_user'
union
select * from t where roles = 'role_teacher' and 
    ((select count(*) from t t1 where t1.email = t.email) = 1)
order by id;

+------+------------+-----------+-----------------------+--------------+
| id   | first_name | last_name | email                 | roles        |
+------+------------+-----------+-----------------------+--------------+
| 9798 | person     | one       | personOne@gmail.com   | ROLE_USER    |
| 9801 | person     | two       | personTwo@gmail.com   | ROLE_TEACHER |
| 9802 | person     | three     | personThree@gmail.com | ROLE_TEACHER |
| 9803 | person     | four      | personFour@gmail.com  | ROLE_USER    |
+------+------------+-----------+-----------------------+--------------+
4 rows in set (0.03 sec)

But this won't work if there are roles that are duplicated for an email.

Thank you for your anwer. The outcome is exactly the way I would like it to work. However, the query is really slow, presumably because of the union and the count in the subquery. It took about 10 seconds in a local db with a user table of (only) 8321 records. — F.Doe, Nov 02 '18 at 14:25

Mysql: Group by with condition

3 Answers3