mysql group_concat group by on multiple fields

Question

I have a Table member with member_id, member_name, club_name, region, zone, email as fields.

I am using the MySQL group_concat function like

SELECT group_concat(distinct m.email
SEPARATOR ', ' ) from member m group by m.club_name

This is working fine. But I would like to be able to group_concat on other fields without creating additional queries.

Is it possible to supply the other fields as parameter?

member_id   member_name club_name   region  zone    email
1           member1           A        1    1   [email protected]
2           member2           A        1    1   [email protected]
3           member3           B        1    1   [email protected]
4           member4           C        1    2   [email protected]
5           member5           D        2    1   [email protected]

**group by club**
[email protected],[email protected]
[email protected]
[email protected]
[email protected]

**group by region**
[email protected], [email protected], [email protected], [email protected]
[email protected]

**group by zone**
[email protected], [email protected], [email protected]
[email protected]

Say every Region has 3 Zones, every zone has more than one club. Now how can I get emails which can be grouped or related to Region, Zone or Club for that matter?

possible duplicate of Multiple GROUP_CONCAT on different fields using MySQL — Blazemonger
– Blazemonger, Commented Feb 3, 2014 at 22:52

peterm · Accepted Answer · 2013-08-08 02:28:20Z

9

It's hard to understand what are you after exactly from your question but you can try

SELECT club_name,
       GROUP_CONCAT(DISTINCT email SEPARATOR ', ' ) emails,
       GROUP_CONCAT(DISTINCT member_name SEPARATOR ', ' ) members
       ... 
 FROM member
GROUP BY club_name

Sample output:

| CLUB_NAME |                                EMAILS |          MEMBERS |
------------------------------------------------------------------------
|     Club1 | [email protected], [email protected], [email protected] | Jhon, Mark, Beth |
|     Club2 |              [email protected], [email protected] |    Helen, Thomas |

Here is SQLFiddle demo

On a side note: providing sample data and desired output in a question like this usually improves your changes of getting your answer faster and that best fits your needs.

UPDATE: You can deeply pack information using GROUP_CONCAT() using different separators if it's what you want

SELECT 'club' group_type, GROUP_CONCAT(details SEPARATOR '|') details
  FROM
(
  SELECT CONCAT(club_name, ';', GROUP_CONCAT(DISTINCT email)) details
    FROM member
   GROUP BY club_name
) a
UNION ALL
SELECT 'region' group_type, GROUP_CONCAT(details SEPARATOR '|') details
  FROM
(
  SELECT CONCAT(region, ';', GROUP_CONCAT(DISTINCT email)) details
    FROM member
   GROUP BY region
) a
UNION ALL
SELECT 'zone' group_type, GROUP_CONCAT(details SEPARATOR '|') details
  FROM
(
  SELECT CONCAT(zone, ';', GROUP_CONCAT(DISTINCT email)) details
    FROM member
   GROUP BY zone
) a

Sample output:

| GROUP_TYPE |                                                                                                DETAILS |
-----------------------------------------------------------------------------------------------------------------------
|       club | A;[email protected],[email protected]|B;[email protected]|C;[email protected]|D;[email protected] |
|     region |     1;[email protected],[email protected],[email protected],[email protected]|2;[email protected] |
|       zone |     1;[email protected],[email protected],[email protected],[email protected]|2;[email protected] |

Here is SQLFiddle demo

If you're using php on the client side you can then easily enough unwind details column into separate records using explode() while you're iterating over the resultset.

edited Aug 8, 2013 at 2:28

answered Aug 7, 2013 at 21:17

peterm

93k16 gold badges157 silver badges162 bronze badges

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

6 Comments

Joshi Over a year ago

I have put the complete example above now, what exactly I want.

peterm Over a year ago

@Pawan What do you mean by ...supply other fields as parameter...? You can always group by any of these fields specifying it in GROUP BY... Or you want get emails grouped by different fields in one resultset (one row per grouping)?

Joshi Over a year ago

Can you elaborate how I can have emails grouped by different fields in one resultset i.e. what I want and can't see it. though it might seem obvious to you.

Joshi Over a year ago

I think one row per grouping what I am looking for.

peterm Over a year ago

@Pawan See updated answer. I made an example. But once again, until you come to terms with yourself what you need exactly and in what form it's very hard to help you shooting in the dark.

|

Collectives™ on Stack Overflow

mysql group_concat group by on multiple fields

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related