MySQL Grouping using group_concat and then Group BY

I have a table like this:

Basically, I want to group all names and give the count of such groupings like:

Since there are 2 users with cooking and cleaning, 1 for washing and 4 for cooking.

I am trying to use group_concat

SELECT group_concat(DISTINCT name) AS name, count(*)
FROM warehouse.test1
GROUP BY guid

However, this will not work as I need to group by the groupings and then get count of those groupings.

I am not getting how to group on the groupings and then get the count.

Thanks in advance and I appreciate any heads up!

UPDATE As per the answer I tried

SELECT   groupings, COUNT(*)
FROM     (SELECT   group_concat(DISTINCT name) AS groupings
          FROM     warehouse.test1
          GROUP BY saguid) t
GROUP BY groupings;

However, I get it as

# groupings, COUNT(*)
'cleaning,cooking', '2'
'cooking', '2'
'washing', '1'

shoudnt count be 4 for cooking?

Answer

I’d wrap this query with another query to coubt the grouped data:

SELECT   groupings, COUNT(*)
FROM     (SELECT   group_concat(DISTINCT name) AS groupings
          FROM     warehouse.test1
          GROUP BY saguid) t
GROUP BY groupings

Advertisement

Answer