Retain rows when using a GROUP BY with GROUP_CONCAT

David Carr

PHP & MySQL Tutorials

Keep rows that would normally be lost when selecting rows using a COUNT and GROUP By. To keep say another column and merge them into a column comma separated by using a GROUP_CONCAT function.

Take this example.

SELECT 
    COUNT(tickets.memberID) AS tickets,
    members.username
 FROM 
    tickets, 
    members
WHERE 
    tickets.memberID=members.memberID    
GROUP BY 
    tickets.memberID        
ORDER BY 
    tickets DESC

This will output the number of tickets each user has, if the subject needs to be displayed with the user its not going to be that simple as each row will have a subject but since its using a GROUP BY only one subject would be returned.<

This is where GROUP_CONCAT comes in, using GROUP_CONCAT all subjects can be collected and comma separated and placed in a single column:

SELECT 
    COUNT(tickets.memberID) AS tickets,
    members.username,
    GROUP_CONCAT(tickets.subject) AS subject
 FROM 
    tickets, 
    members
WHERE 
    tickets.memberID=members.memberID    
GROUP BY 
    tickets.memberID        
ORDER BY 
    tickets DESC

With GROUP_CONCAT in place the above query will now return all subjects from the rows that would be returned if no GROUP BY were being used.
An extremely useful function indeed.

Fathom Analytics $10 discount on your first invoice using this link

David Carr - Laravel Developer

Hi, I’m David Carr

A Senior Developer at Vivedia
I love to use the TALL stack (Tailwind CSS, Alpine.js, Laravel, and Laravel Livewire)

I enjoy writing tutorials and working on Open Source packages.

I also write books. I'm writing a new book Laravel Testing Cookbook, This book focuses on testing coving both PestPHP and PHPUnit.

Sponsor me on GitHub

Subscribe to my newsletter

Subscribe and get my books and product announcements.

Laravel Testing Cookbook

Help support the blog so that I can continue creating new content!

Fathom Analytics $10 discount on your first invoice using this link

Subscribe to my newsletter

Subscribe and get my books and product announcements.

© 2006 - 2023 DC Blog. All code MIT license. All rights reserved.