Black Friday sale is now on! 50% off Laravel: The Modular Way. Learn more

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.

    Laravel Modules Book by David Carr

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

    Subscribe to my newsletter

    Subscribe and get my books and product announcements.

    © 2009 - 2022 DC Blog. All code MIT license. All rights reserved.