DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • MySQL Multi-Source Replication
  • An In-Depth Look at Oracle MySQL HeatWave
  • SQL Query Performance Tuning in MySQL
  • Kubernetes Evolution: Transitioning from etcd to Distributed SQL

Trending

  • Getting Started With Prometheus Workshop: Instrumenting Applications
  • Deploy a Session Recording Solution Using Ansible and Audit Your Bastion Host
  • Java Parallel GC Tuning
  • Choosing the Appropriate AWS Load Balancer: ALB vs. NLB
  1. DZone
  2. Data Engineering
  3. Databases
  4. The Power of MySQL’s GROUP_CONCAT

The Power of MySQL’s GROUP_CONCAT

Peter Zaitsev user avatar by
Peter Zaitsev
·
Oct. 24, 13 · Interview
Like (0)
Save
Tweet
Share
11.12K Views

Join the DZone community and get the full member experience.

Join For Free

this post comes from michael rikmas at the mysql performance blog.

in the very early days of percona vadim wrote very nice post about group_concat .

but i want to show you a bit more about it.

when is group_concat useful? usually while working with support customers i recommend it when you have aggregation of many-to-many info. it makes the view simpler and more beautiful and it doesn’t need much effort to make it work.

some simple examples:

this is a test table:

create table `group_c` (
`parent_id` int(11) default null,
`child_id` int(11) default null
) engine=innodb;
insert into group_c(parent_id, child_id)
values (1,1),(1,2),(1,3),(2,1),(2,4),(1,4),(2,6),(3,1),(3,2),(4,1),(4,1),(1,1),(5,0);

without grouping info the only way you can check things is:

mysql> select distinct
    -> parent_id, child_id
    -> from group_c
    -> order by parent_id;
+-----------+----------+
| parent_id | child_id |
+-----------+----------+
|         1 |        1 |
|         1 |        2 |
|         1 |        3 |
|         1 |        4 |
|         2 |        1 |
|         2 |        3 |
|         2 |        4 |
|         2 |        6 |
|         3 |        1 |
|         3 |        2 |
|         4 |        1 |
|         5 |        0 |
+-----------+----------+
12 rows in set (0.00 sec)

but it looks much better and easier to read with group_concat:

mysql> select distinct
    -> parent_id, group_concat(distinct child_id order by child_id) as child_id_list
    -> from group_c
    -> group by parent_id
    -> order by parent_id;
+-----------+---------------+
| parent_id | child_id_list |
+-----------+---------------+
|         1 | 1,2,3,4       |
|         2 | 1,3,4,6       |
|         3 | 1,2           |
|         4 | 1             |
|         5 | 0             |
+-----------+---------------+
5 rows in set (0.00 sec)

easy? let’s go to production usage and some “real” examples :)

assume you have 4 support engineers who were working with 6 customers this week on 15 issues.

as it usually happens: everyone (sure, except those who are on vacation :) ) worked on everything with everybody.

how you would represent it?

here is my way:

create test tables:

  • engineers (id, name, surname, url) – list of engineers
  • customers (id, company name, url) – list of customers
  • issues (id, customer_id, description) – list of issues assigned to customers
  • workflow (id, engineer_id, issue_id) – list of actions: issues and engineers who worked on them
    -- engineers
    create table engineers (
    id smallint unsigned not null auto_increment,
    e_name varchar(30) not null,
    e_surname varchar(30) not null,
    url varchar(255) not null,
    primary key (id)
    ) engine=innodb;
    -- customers
    create table customers (
    id smallint unsigned not null auto_increment,
    company_name varchar(30) not null,
    url varchar(255) not null,
    primary key (id)
    ) engine=innodb;
    -- issues (issue-customer)
    create table issues (
    id mediumint unsigned not null auto_increment,
    customer_id varchar(30) not null,
    description text,
    primary key (id)
    ) engine=innodb;
    -- workflow (action: engineer-issue(customer))
    create table workflow (
    action_id int unsigned not null auto_increment,
    engineer_id smallint unsigned not null,
    issue_id smallint unsigned not null,
    primary key (action_id)
    ) engine=innodb;
    insert into engineers (e_name, e_surname, url)
    values
    ('miguel', 'nieto', 'http://www.percona.com/about-us/our-team/miguel-angel-nieto'),
    ('marcos', 'albe', 'http://www.percona.com/about-us/our-team/marcos-albe'),
    ('valerii', 'kravchuk', 'http://www.percona.com/about-us/our-team/valerii-kravchuk'),
    ('michael', 'rikmas', 'http://www.percona.com/about-us/our-team/michael-rikmas');
    insert into customers (company_name, url)
    values
    ('ot','http://www.ovaistariq.net/'),
    ('pz','http://www.peterzaitsev.com/'),
    ('vk','http://mysqlentomologist.blogspot.com/'),
    ('fd','http://www.lefred.be/'),
    ('as','http://mysqlunlimited.blogspot.com/'),
    ('ss','https://www.flamingspork.com/blog/');
    insert into issues(customer_id, description)
    values
    (1,'fix replication'),
    (2,'help with installation of percona cluster'),
    (3,'hardware suggestions'),
    (4,'error: no space left'),
    (5,'help with setup daily backup by xtrabackup'),
    (6,'poke sales about support agreement renewal'),
    (4,'add more accounts for customer'),
    (2,'create hot fix of bug 1040735'),
    (1,'query optimisation'),
    (1,'prepare custom build for solaris'),
    (2,'explain about percona monitoring plugins'),
    (6,'prepare access for customer servers for future work'),
    (5,'decribe load balancing for pt-online-schema-change'),
    (4,'managing deadlocks'),
    (1,'suggestions about buffer pool size');
    insert into workflow (engineer_id, issue_id)
    values (1,1),(4,2),(2,3),(1,4),(3,5),(2,6),(3,7),(2,8),(2,9),(1,10),(3,11),(2,12),(2,13),(3,14),(1,15),(1,9),(4,14),(2,9),(1,15),(3,10),(4,2),(2,15),(4,8),(4,4),(3,11),(1,7),(3,7),(1,1),(1,9),(3,4),(4,3),(1,5),(1,7),(1,4),(2,4),(2,5);

examples:

list of issues for each engineer (group_concat):

mysql> select
    ->  concat (e_name, ' ', e_surname) as engineer,
    ->  group_concat(distinct issue_id, ' (', c.company_name,')' order by issue_id separator ', ' ) as 'issue (customer)'
    -> from
    ->  workflow w,
    ->  engineers e,
    ->  customers c,
    ->  issues i
    -> where
    ->  w.engineer_id = e.id
    ->  and w.issue_id = i.id
    ->  and i.customer_id = c.id
    -> group by
    ->  e.id
    -> order by
    ->  e_name, e_surname;
+------------------+---------------------------------------------------------------------------+
| engineer         | issue (customer)                                                          |
+------------------+---------------------------------------------------------------------------+
| marcos albe      | 3 (vk), 4 (fd), 5 (as), 6 (ss), 8 (pz), 9 (ot), 12 (ss), 13 (as), 15 (ot) |
| michael rikmas   | 2 (pz), 3 (vk), 4 (fd), 8 (pz), 14 (fd)                                   |
| miguel nieto     | 1 (ot), 4 (fd), 5 (as), 7 (fd), 9 (ot), 10 (ot), 15 (ot)                  |
| valerii kravchuk | 4 (fd), 5 (as), 7 (fd), 10 (ot), 11 (pz), 14 (fd)                         |
+------------------+---------------------------------------------------------------------------+
4 rows in set (0.00 sec)

list of engineers for each customer (group_concat inside of group_concat):

mysql> select
    ->  c.company_name as company,
    ->  group_concat(distinct issue_id, ' (', engineer_list, ')' order by issue_id separator ', ' ) as issue
    -> from
    ->  workflow w,
    ->  engineers e,
    ->  customers c,
    ->  issues i,
    ->  (select
    ->    i.id as i_id,
    ->    group_concat(distinct concat(e_name, ' ', e_surname) order by e_name separator ', ') as engineer_list
    ->   from
    ->    workflow w,
    ->    engineers e,
    ->    issues i
    ->   where
    ->    w.engineer_id = e.id
    ->    and w.issue_id = i.id
    ->   group by
    ->    i.id) as e_list
    -> where
    ->  w.engineer_id = e.id
    ->  and w.issue_id = i.id
    ->  and i.customer_id = c.id
    ->  and w.issue_id = e_list.i_id
    -> group by
    ->  c.id
    -> order by
    ->  c.company_name;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| company | issue (engineer)                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| as      | 5 (marcos albe, miguel nieto, valerii kravchuk), 13 (marcos albe)                                                                          |
| fd      | 4 (marcos albe, michael rikmas, miguel nieto, valerii kravchuk), 7 (miguel nieto, valerii kravchuk), 14 (michael rikmas, valerii kravchuk) |
| ot      | 1 (miguel nieto), 9 (marcos albe, miguel nieto), 10 (miguel nieto, valerii kravchuk), 15 (marcos albe, miguel nieto)                       |
| pz      | 2 (michael rikmas), 8 (marcos albe, michael rikmas), 11 (valerii kravchuk)                                                                 |
| ss      | 6 (marcos albe), 12 (marcos albe)                                                                                                          |
| vk      | 3 (marcos albe, michael rikmas)                                                                                                            |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

php/html? why not? it’s easy :)

source code:

<?php
$mysqli = new mysqli("localhost", "db_user", "password", "db_name");
if (mysqli_connect_errno()) {
  printf("connect failed: %s\n", mysqli_connect_error());
  exit();
}
$query = "select
 i.id,
 group_concat(distinct '<a target=_blank href=', c.url, '>', concat(c.company_name), '</a>' order by e_name separator ', ') as company,
 i.description,
 group_concat(distinct '<a target=_blank href=', e.url, '>', concat(e_name, ' ', e_surname), '</a>' order by e_name separator ', ') as engineer_list
from
 workflow w,
 engineers e,
 customers c,
 issues i
where
 w.engineer_id = e.id
 and w.issue_id = i.id
 and i.customer_id = c.id
group by
 i.id
order by
 i.id";
$result = $mysqli->query($query);
while($row = $result->fetch_array())
{
  $rows[] = $row;
}
echo "<table border=1 cellpadding=5 style=\"border-collapse: collapse; border: 1px solid black;\">";
foreach($rows as $row)
{
  echo "<tr>
  		<td>".$row["id"].'</td>
  		<td>'.$row["company"].'</td>
  		<td>'.$row["description"].'</td>
  		<td>'.$row["engineer_list"].'</td>
  	</tr>';
}
echo "</table>";
$result->close();
$mysqli->close();
?>

result:

1 ot fix replication miguel nieto
2 pz help with installation of percona cluster michael rikmas
3 vk hardware suggestions marcos albe , michael rikmas
4 fd error: no space left marcos albe , michael rikmas , miguel nieto , valerii kravchuk
5 as help with setup daily backup by xtrabackup marcos albe , miguel nieto , valerii kravchuk
6 ss poke sales about support agreement renewal marcos albe
7 fd add more accounts for customer miguel nieto , valerii kravchuk
8 pz create hot fix of bug 1040735 marcos albe , michael rikmas
9 ot query optimisation marcos albe , miguel nieto
10 ot prepare custom build for solaris miguel nieto , valerii kravchuk
11 pz explain about percona monitoring plugins valerii kravchuk
12 ss prepare access for customer servers for future work marcos albe
13 as decribe load balancing for pt-online-schema-change marcos albe
14 fd managing deadlocks michael rikmas , valerii kravchuk
15 ot suggestions about buffer pool size marcos albe , miguel nieto

that’s a power of group_concat !



MySQL

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • MySQL Multi-Source Replication
  • An In-Depth Look at Oracle MySQL HeatWave
  • SQL Query Performance Tuning in MySQL
  • Kubernetes Evolution: Transitioning from etcd to Distributed SQL

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: