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   email1@example.com
2           member2           A        1    1   email2@example.com
3           member3           B        1    1   email3@example.com
4           member4           C        1    2   email4@example.com
5           member5           D        2    1   email5@example.com

**group by club**
email1@example.com,email2@example.com
email3@example.com
email4@example.com
email5@example.com

**group by region**
email1@example.com, email2@example.com, email3@example.com, email4@example.com
email5@example.com

**group by zone**
email1@example.com, email2@example.com, email3@example.com
email5@example.com

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](http://stackoverflow.com/questions/7506750/multiple-group-concat-on-different-fields-using-mysql) — Blazemonger, Feb 03 '14 at 22:52

peterm · Accepted Answer · 2013-08-08T02:28:20.393

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 | m1@mail.com, m2@mail.com, m3@mail.com | Jhon, Mark, Beth |
|     Club2 |              m4@mail.com, m5@mail.com |    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;email1@example.com,email2@example.com|B;email3@example.com|C;email4@example.com|D;email5@example.com |
|     region |     1;email1@example.com,email2@example.com,email3@example.com,email4@example.com|2;email5@example.com |
|       zone |     1;email1@example.com,email2@example.com,email3@example.com,email5@example.com|2;email4@example.com |

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.

I have put the complete example above now, what exactly I want. — Joshi, Aug 07 '13 at 21:31
@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)? — peterm, Aug 07 '13 at 23:45
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, Aug 08 '13 at 01:41
@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. — peterm, Aug 08 '13 at 02:30
Thanks. I am sure I will be able to adept your example at JSFidde to my needs. — Joshi, Aug 08 '13 at 03:50

mysql group_concat group by on multiple fields

1 Answers1

Linked