MySQL, how can I group on group_concat?

Question

Here is my current query (confidential data replaced and where clauses removed for simplicity):

select a.id, group_concat(v.value) from asset a, values v where a.id=v.id group by a.case_id;

The current results appear as:

a.id   v.value
123   a,b
234   a
456   a,b,c
789   d,e
237   a

What I want it to do is group by and count the results of the group_concat, so it should look like:

v.value    count
a,b           1
a              2
a,b,c        1
d,e           1

I have tried unsuccessfully to group on the field name I give to the group_concat, it keeps on saying 'unable to group by 'name'

I have to do this directly in the query not in the code. Any suggestions?

my mistake, it should have been group by a.id not a.case_id. I don't need the individual id in the result set. — user2029890
– user2029890, Commented Jan 31, 2013 at 17:23

Strawberry · Accepted Answer · 2013-01-31 17:39:30Z

3

SELECT gc_values
     , COUNT(*)
  FROM 
     ( SELECT a.id
            , GROUP_CONCAT(v.value) gc_values
         FROM asset a
         JOIN `values` v 
           ON v.id = a.id
        GROUP
           BY a.id
     ) x
 GROUP
    BY gc_values;

edited Jan 31, 2013 at 17:39

answered Jan 31, 2013 at 17:13

Strawberry

34k14 gold badges43 silver badges57 bronze badges

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

4 Comments

user2029890 Over a year ago

Thank you for the quick response. However, your code appears to count the number of results for each individual value of the group_concat. What I want is the grouped results of the group_concat. In other words, how many times does just 'a' appear, or how may times do we have 'a,b,c'

Strawberry Over a year ago

Er, no. I'm pretty sure this fulfils the spec. !?!

user2029890 Over a year ago

Interesting. However, I'm getting a syntax error near ' COUNT(*). That's even when I copy your exact code

Strawberry Over a year ago

Oh yeah - values is a reserved word in 5.6

underrun · Accepted Answer · 2013-01-31 17:20:40Z

0

you could do:

select grouped_values, count(*) from [subselect aliasing group_concat] group by grouped_values

edited Jan 31, 2013 at 17:20

answered Jan 31, 2013 at 17:15

underrun

6,8712 gold badges44 silver badges54 bronze badges

Collectives™ on Stack Overflow

MySQL, how can I group on group_concat?

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related