Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted January 9, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Applying MySQL’s GROUP_CONCAT Function to Multiple Columns

By Rob Gravelle

In last month’s Why You Should be Using the MySQL GROUP_CONCAT Function article, I highlighted some of the benefits to using MySQL’s GROUP_CONCAT function.  What was missing from that introduction was the full breadth of what the GROUP_CONCAT function can do.  So this follow-up will delve into the full syntax and provide some guidance on how to use the GROUP_CONCAT function to chain together data from multiple columns in a way that’s both easy to read and parse with a script or application.

A Syntax Primer

In order to reap the GROUP_CONCAT function’s full benefit, it helps to have a general understanding of what it can do.  As you look over the full syntax, I’d like to draw your attention to the following points:

  1. The optional extra expressions.
  2. The ORDER BY clause.
  3. The SEPARATOR argument.

Here they are within the syntax definition:

GROUP_CONCAT([DISTINCT] expr [,expr ...] (1)
              [ORDER BY (2){unsigned_integer | col_name | expr}
                  [ASC | DESC] [,col_name ...]]
              [SEPARATOR str_val] (3))

Adding a Second Column To the “Multiple Horror Movie Rentals” Query

The main query of the Why You Should be Using the MySQL GROUP_CONCAT Function article returned a list of customers who had rented three or more horror movies.  The GROUP_CONCAT function was utilized to concatenate the list of movie titles.  We can add a second column, such as the film ID, to the list as another argument to the function.  Arguments can be any type of expression, including string literals, so you can include a delimiter of your choice between the two fields (I went with a comma).  Note that this delimiter is different than the function delimiter, which separates each grouped expression – i.e. “film_id, title - delimiter - film_id, title - delimiter - film_id, title - delimiter etc…”.  Since it also defaults to a comma, we can change it to something other than our field delimiter.  For that I chose the vertical bar ( | ) character.

Here is the revised query with the updated GROUP_CONCAT line in red, along with the results:

SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
        A.phone, 
        date(R.rental_date) AS rental_date,
        GROUP_CONCAT(F.film_id, ',', F.title SEPARATOR '|') AS 'ids & 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;
customer         phone         rental_date  ids & titles                                                           rentals_count                
 --------------------------------------------------------------------------------------------------------------------------------
 ADAM, NATHANIEL  111177206479  2005-08-22  334,FREDDY STORM|24,ANALYZE HOOSIERS|854,STRANGERS GRAFFITI                         3 
 ANDREW, JOSE     961370847344  2005-08-02  527,LOLA AGENT|415,HIGH ENCINO|313,FIDELITY DEVIL|275,EGYPT TENENBAUMS              4
 AQUINO, OSCAR    474047727727  2005-07-28  4,AFFAIR PREJUDICE|258,DRUMS DYNAMITE|856,STREETCAR INTENTIONS|275,EGYPT TENENBAUMS 4
 ARTIS, CARL      20064292617   2005-07-27  313,FIDELITY DEVIL|749,RULES HUMAN|92,BOWFINGER GABLES|995,YENTL IDAHO              4
 BARBEE, CLAYTON  380077794770  2005-05-26  65,BEHAVIOR RUNAWAY|870,SWARM GOLD|535,LOVE SUICIDES                                3
 etc...

Handling NULL Values

As stated in the MySQL docs, by default, GROUP_CONCAT “returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.”

Special care may be required in the handling of Null values if:

  1. you want to include null values in the expression
  2. you don’t want extra commas

To get a taste of GROUP_CONCAT’s handling of Nulls, we can query the rental table.  The return_date column is Null while a customer still has a movie in their possession, i.e. has rented a movie but has not yet returned it. 

First, we’ll omit the return_date column so that no Null values are passed to the GROUP_CONCAT function:

SELECT CONCAT(C.last_name, ', ', C.first_name) AS customer,
        GROUP_CONCAT(F.title, ',', date(rental_date)
                     ORDER BY rental_date DESC, title SEPARATOR '|') AS rental_history
 FROM rental R 
     INNER JOIN customer  C ON R.customer_id = C.customer_id
     INNER JOIN address   A ON C.address_id = A.address_id
     INNER JOIN inventory I ON R.inventory_id = I.inventory_id
     INNER JOIN film      F ON I.film_id = F.film_id
 GROUP BY R.customer_id
 ORDER BY customer;

We only need to focus on the first row because RAFAEL ABNEY just happens to have a movie out.

Customer          rental_history
 ---------------------------------------------------------------------------------------------------------------------------
 ABNEY, RAFAEL     CONVERSATION DOWNHILL,2006-02-14|FREDDY STORM,2005-08-23|CLASH FREDDY,2005-08-22|BLACKOUT PRIVATE...

Make a mental note of the first movie, CONVERSATION DOWNHILL, and watch what happens to the results when we add the return_date column to the GROUP_CONCAT field list:

SELECT CONCAT(C.last_name, ', ', C.first_name) AS customer,
        GROUP_CONCAT(F.title, ',', date(rental_date) , ',', date(return_date) 
                     ORDER BY rental_date DESC, title SEPARATOR '|') AS rental_history
 FROM rental R 
     INNER JOIN customer  C ON R.customer_id = C.customer_id
     INNER JOIN address   A ON C.address_id = A.address_id
     INNER JOIN inventory I ON R.inventory_id = I.inventory_id
     INNER JOIN film      F ON I.film_id = F.film_id
 GROUP BY R.customer_id
 ORDER BY customer;

No more CONVERSATION DOWNHILL!

Customer          rental_history
 ---------------------------------------------------------------------------------------------------------------------------
 ABNEY, RAFAEL,    FREDDY STORM,2005-08-23,2005-08-28|CLASH FREDDY,2005-08-22,2005-08-28|BLACKOUT PRIVATE,2005-08-21,2005...
 

Luckily, it’s easy enough to eliminate Null values by passing the column to ifnull(), as in ifnull(date(return_date), '').  The problem with that approach is that it will leave a trailing comma:

CONVERSATION DOWNHILL,2006-02-14,|FREDDY STORM,2005-08-23,2005-08-28...

A better way to deal with Nulls is to let the CONCAT_WS function take care of the concatenation duties.  It won’t omit records with null values or leave a trailing delimiter:

SELECT CONCAT(C.last_name, ', ', C.first_name) AS customer,
        GROUP_CONCAT(CONCAT_WS(',', F.title, date(rental_date), date(return_date)) 
                     ORDER BY rental_date DESC, title SEPARATOR '|') AS rental_history
 FROM rental R 
     INNER JOIN customer   C ON R.customer_id = C.customer_id
     INNER JOIN address    A ON C.address_id = A.address_id
     INNER JOIN inventory  I ON R.inventory_id = I.inventory_id
     INNER JOIN film       F ON I.film_id = F.film_id
 GROUP BY R.customer_id
 ORDER BY customer;


Customer         rental_history
 ---------------------------------------------------------------------------------------------------------------------------
 ABNEY, RAFAEL    CONVERSATION DOWNHILL,2006-02-14|FREDDY STORM,2005-08-23,2005-08-28|CLASH FREDDY,2005-08-22,2005-08-28...

Of course, it really depends on what you’re trying to accomplish.  For columns that are not last in the list, it may be advisable to leave the delimiter. 

Conclusion

MySQL’s GROUP_CONCAT function is the perfect complement to the GROUP BY clause to include group data in one row.  Besides allowing for the visual scanning of the data from left to right, it also greatly simplifies reading the data from an external application so that it can access related group data in fewer operations. 

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM