How to make MYSQL GROUP_CONCAT result distinct and ordered by number occurrences

Question

so i have a table that looks like the following

 tool_id   |  tool_user 
  hammer        adam
  hammer        adam
  hammer        sandra
 screwdriver    sandra

So i am trying to select the tools ordered by number of usages (record occurrences) and also concat all the users in a field, the tricky part is to count and sort the users by number of occurences as well

So far, i was able to get the tools by usages and order them but i am not sure how to sort the users by number of occurrences.

The expected result of the select statment is:

tool          users
hammer       adam,sandra
screwdriver  sandra

Current the SQL statement looks like following:

SELECT tool_id, GROUP_CONCAT(tool_user SEPARATOR ",") AS tool_users, COUNT(*) AS count FROM tools_table GROUP BY tool_id ORDER BY count DESC LIMIT 5

Bill Karwin · Accepted Answer · 2019-11-18T23:45:56.153

2

First do a query to reduce the rows to one per tool and user:

select tool_id, tool_user, count(*) as count from tools_table group by tool_id, tool_user;
+-------------+-----------+-------+
| tool_id     | tool_user | count |
+-------------+-----------+-------+
| hammer      | adam      |     2 |
| hammer      | sandra    |     1 |
| screwdriver | sandra    |     1 |
+-------------+-----------+-------+

Then you can wrap that query in another query to do the group-concat:

select tool_id, group_concat(tool_user order by count desc) as users from (
  select tool_id, tool_user, count(*) as count from tools_table group by tool_id, tool_user
) as t
group by tool_id;
+-------------+-------------+
| tool_id     | users       |
+-------------+-------------+
| hammer      | adam,sandra |
| screwdriver | sandra      |
+-------------+-------------+

edited Nov 18 '19 at 23:45

answered Nov 18 '19 at 23:29

Bill Karwin

538,548
86
673
828

Thank you, I've fixed the above. – Bill Karwin Nov 18 '19 at 23:46
As nick mentioned, the first query needed order by count, otherwise worked good. But it does not seem to let me to select from another select, receiving the following error `an expression was expected near (` – pabloBar Nov 18 '19 at 23:48
I tested the above with MySQL 5.7.27. What version are you using? `SELECT @@version;` – Bill Karwin Nov 19 '19 at 00:02
Actually it is was working, my bad. Thanks a lot :) – pabloBar Nov 19 '19 at 00:08
A side question , is it possible to limit number of users also ? – pabloBar Nov 19 '19 at 01:12
Sounds like that's a variation of the [tag:greatest-n-per-group] question. See for example https://stackoverflow.com/a/1442867/20860 – Bill Karwin Nov 19 '19 at 01:34
This is completely different solution, isnt there a more simple way to limit the users with our current approach? i was expecting do something with LIMIT, but it doesnt seem to work. – pabloBar Nov 19 '19 at 18:54
LIMIT doesn't work because LIMIT doesn't limit per group, it limits the whole result set. No, there's not a simpler solution. I wrote _three_ different solutions to the four-items-per-group type of query in the answer I linked to. – Bill Karwin Nov 19 '19 at 18:59
I managed to rewrite the original query to match the one in your example but i still was not able to figure out how to limit number of users. My query looks like this: `SELECT i1.tool_id, GROUP_CONCAT(DISTINCT i1.tool_user ) AS tool_users, COUNT(*) AS tool_count FROM tool_db i1 LEFT OUTER JOIN tool_db i2 ON (i1.tool_user = i2.tool_user AND i1.tool_id = i2.tool_id ) GROUP BY i1.tool_id ORDER BY COUNT(*) DESC LIMIT 5` which is working fine, but gives incorrect order when adding `HAVING COUNT(*) < 4` as you have done in your example. Maybe your example requires incremental primary key ? – pabloBar Nov 19 '19 at 21:12
Yes, the first solution in that answer assumes you have a some kind of unique key. If you don't, then use one of the other solutions. It would be best if you are using MySQL 8.0, because they implemented window functions. That's shown in the third solution. – Bill Karwin Nov 19 '19 at 21:25
I have unique key, but does it need to be a incremental key for the first solution to work? – pabloBar Nov 19 '19 at 21:34
It needs to be unique and ordered for the first solution to work. It doesn't have to be an auto-increment. – Bill Karwin Nov 19 '19 at 21:52
Alright noted, i think i found some kind of ugly fix for now by using `SUBSTRING_INDEX`. Btw how about performance for the query above, will it an issue when having alot of records ? – pabloBar Nov 19 '19 at 22:39
All queries have issues when they run against a larger data set. You probably need some indexes to optimize the query but that's a different question. – Bill Karwin Nov 19 '19 at 23:40

score 1 · Answer 2 · answered Nov 18 '19 at 23:48

Simply add an Distinct

SELECT 
  tool_id, GROUP_CONCAT(DISTINCT tool_user SEPARATOR ",") AS tool_users
FROM tools_table 
GROUP BY tool_id 
ORDER BY COUNT(DISTINCT tool_user) DESC LIMIT 5

Gives You

tool_id       tool_users    
hammer        adam,sandra   
screwdriver   sandra

Example https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=972b29cff86121dc497d1827fbc3f1ab

How to make MYSQL GROUP_CONCAT result distinct and ordered by number occurrences

2 Answers2