Why should the MySQL GROUP CONCAT function be used?

This is the function MySQL GROUP CONCAT and how it can change the work with query results. Especially if the database is the data source for the application.

Database

I will use the Sakila sample database as a reference. The database includes a number of related tables on the subject of cinema: from actors and film studios to video distribution points. The full structure of this database can be seen on the MySQL development site.

Outdated grouping method

The GROUP BY operator is an excellent tool for selecting related data. But it is not suitable for accurate data sorting.

Let’s imagine that we are the owners of a film distribution point and wish to reward those customers who have taken many horror movies. To do this, we need to know which films each client has rented. One way to do this is to move the GROUP BY SELECT instruction to an attached request that returns user IDs that meet all requirements. It is then possible to limit the results of an external request to those clients whose IDs are part of the internal result set.

Below is the SQL code that performs this work without MySQL GROUP CONCAT SEPARATOR:

SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
A.phone,
F.title,
date(R.rental_date) AS rental_date
FROM sakila.rental R
LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
LEFT JOIN sakila.film F ON I.film_id = F.film_id
LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
LEFT JOIN sakila.category C ON FC.category_id = C.category_id
LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
LEFT JOIN sakila.address A ON CU.address_id = A.address_id
WHERE CU.customer_id in
(SELECT CU.customer_id)
FROM rental R
LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
LEFT JOIN sakila.film F ON I.film_id = F.film_id
LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
LEFT JOIN sakila.category C ON FC.category_id = C.category_id
WHERE C.name = "Horror"
GROUP BY CU.customer_id
HAVING COUNT(CU.customer_id) >= 3)
AND C.name = "Horror"
ORDER BY customer, title, rental_date DESC;

Receive the first three clients with the titles of the films rented and the dates:

customer phone title rental_date
—————————————————————-
ADAM, NATHANIEL 111177206479 ANALYZE HOOSIERS 2005-08-19
ADAM, NATHANIEL 111177206479 FREDDY STORM 2005-08-22
ADAM, NATHANIEL 111177206479 STRANGERS GRAFFITI 2005-08-23
ANDREW, JOSE 961370847344 EGYPT TENENBAUMS 2005-07-31
ANDREW, JOSE 961370847344 FIDELITY DEVIL 2005-05-30
ANDREW, JOSE 961370847344 HIGH ENCINO 2005-07-07
ANDREW, JOSE 961370847344 LOLA AGENT 2005-08-02
AQUINO, OSCAR 47404727727 AFFAIR PREJUDICE 2005-07-28
AQUINO, OSCAR 474047727727 DRUMS DYNAMITE 2005-06-20
AQUINO, OSCAR 474047727727 EGYPT TENENBAUMS 2005-07-28
AQUINO, OSCAR 474047727727 STREETCAR INTENTIONS 2005-08-01
and so on...

It works even though internal and external SQL WHERE operators are repeated. But that’s not the point – an application that receives query results must track client names to know when to go to the next one. I’ve done this many times and there was always confusion about the results.

How to create a grouped list with GROUP CONCAT

The MySQL GROUP CONCAT function is not new. It unites all non-zero values from the group and returns them as a string with commas separators. In combination with the GROUP BY operator, it allows you to place the grouped data in one line.

Let’s rewrite our code using the GROUP_CONCAT function:

SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
A.phone,
date(R.rental_date) AS rental_date,
GROUP_CONCAT(F.title) AS titles,
COUNT(*) AS rentals_count
FROM sakila.rental R
LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
LEFT JOIN sakila.film F ON I.film_id = F.film_id
LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
LEFT JOIN sakila.category C ON FC.category_id = C.category_id
LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
LEFT JOIN sakila.address A ON CU.address_id = A.address_id
WHERE C.name = "Horror"
GROUP BY R.customer_id
HAVING rentals_count >= 3
ORDER BY customer, title, rental_date DESC;

As you can see, with MySQL GROUP CONCAT the problem of extra data is solved, because you no longer need to filter the results.

The rented movies are listed in the “titles” column:

customer phone rental_date titles rentals_count
———————————————————————————————————————————
ADAM, NATHANIEL 111177206479 2005-08-22 FREDDY STORM,ANALYZE HOOSIERS,STRANGERS GRAFFITI 3
ANDREW, JOSE 961370847344 2005-07-31 EGYPT TENENBAUMS,LOLA AGENT,FIDELITY DEVIL,HIGH ENCINO 4
AQUINO, OSCAR 47404772727 2005-07-28 EGYPT TENENBAUMS,AFFAIR PREJUDICE,STREETCAR INTENTIONS,DRUMS DYNAMITE 4
CARL 20064292617 2005-08-18 BOWFINGER GABLES,RULES HUMAN,YENTL IDAHO,FIDELITY DEVIL 4
BARBEE, CLAYTON 38007794770 2005-05-26 BEHAVIOR RUNAWAY,LOVE SUICIDES,SWARM GOLD 3
and so on...

Besides, one more task has been solved – displaying grouped data in one line. This has a positive impact on the application’s operation, as access to the grouped data is provided by one operation.

This is a fairly simple process using the line break function MySQL GROUP CONCAT, which is implemented in most programming languages.

For example, in PHP this function is called “explode”. As parameters, the function accepts the separator and the string, and returns data as an array. Below is an example of how movie titles can be obtained using the above query:

//resultant set extraction
$res=$mysqli--->query($select_statement);
/Iteration for each line
while ($row = $res->fetch_array(MYSQLI_ASSOC)) {
// this manual separates the titles line
//comma delimited
$titles_array = explode(',', $row['titles']);
// work with an array of names...
}

Another advantage of using GROUP_CONCAT is that the string value can be used as part of the IN operator:

$res_films = $mysqli->query("SELECT * FROM sakila.film WHERE title = IN ($titles_array)");
// working with $res_films...

Conclusion

Do you want to use commas as separators? Want to sort the items? The MySQL GROUP CONCAT function is suitable for both tasks.

GROUP_CONCAT function in SQL