This week's gem: MySQL GROUP_CONCAT

On Saturday and Sunday, I worked through some Leetcode SQL problems and found a problem that required the GROUP_CONCAT function. At first, the problem seemed very difficult and scary, but I managed to solve it. I thought the function was cool, so I decided to use it in my New York Times database.

My understanding is that the GROUP_CONCAT function allows you to concatenate the rows in a column into a single line of text with separators of your choice.

In the first query, I wanted to create a 'list' of all of the publishers who had a book on the Hardcover Fiction lists for each month in 2020.

Code

SELECT 
    MONTHNAME(le.date_published) AS `Month`,
    GROUP_CONCAT(DISTINCT p.publisher_name ORDER BY p.publisher_name SEPARATOR ', ') AS Publishers
FROM list_entries le
LEFT JOIN publishers p ON le.publisher_id = p.publisher_id
# Hardcover Fiction list has the list_id 2
WHERE le.list_id = 2 AND YEAR(le.date_published) = 2020
GROUP BY MONTH(le.date_published);

Results

As you can see, the query results are not very useful; the concatenation of publishers is very, but it was definitely a good way to practice using the function.

The second query I created has more useful results because I could share all the books the authors wrote.

Code

SELECT
    author_name AS 'Author',
    GROUP_CONCAT(title ORDER BY title SEPARATOR ', ') AS 'Book Titles',
    COUNT(book_id) AS 'Total Books'
FROM book_author
GROUP BY author_name
HAVING COUNT(book_id) > 1
ORDER BY COUNT(book_id) DESC;

Results

I had a little bit of fun with the GROUP_CONCAT function, and I am excited to see the other ways I can use it in the future.