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.
For the past 12 years, I’ve been developing applications for the web using mostly PHP. I do this for a living and love what I do as every day there is something new and exciting to learn.
In my spare time, the web development community is a big part of my life. Whether managing online programming groups and blogs or attending a conference, I find keeping involved helps me stay up to date. This is also my chance to give back to the community that helped me get started, a place I am proud to be apart of.
Besides programming I love spending time with friends and family and can often be found together going out catching the latest movie, staying in playing games on the sofa or planning a trip to someplace I’ve never been before.