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-18 23:45:56Z

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, 2019 at 23:45

answered Nov 18, 2019 at 23:29

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

pabloBar Over a year ago

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 (

Bill Karwin Over a year ago

I tested the above with MySQL 5.7.27. What version are you using? SELECT @@version;

pabloBar Over a year ago

A side question , is it possible to limit number of users also ?

Bill Karwin Over a year ago

Sounds like that's a variation of the greatest-n-per-group question. See for example stackoverflow.com/a/1442867/20860

pabloBar Over a year ago

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.

|

nbk · Accepted Answer · 2019-11-18 23:48:11Z

1

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

answered Nov 18, 2019 at 23:48

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related