CODEWITHSUNDEEP
mysqlsql
HerrimanCoder
HerrimanCoder

Reputation: 7208

How to concatenate column values aggregated out of a group by?

I have this MySQL query that works almost perfectly:

select ConfirmationNumber, ReservationDate, ifnull(CASE TableNumber WHEN 0 THEN 'UNASSIGNED' ELSE TableNumber END,'UNASSIGNED') AS 'Table', CONCAT(LastName, ', ', FirstName) AS 'Customer', Email, Phone, PublicNotes, sum(SleighSeats) + sum(CabSeats) AS Seats
from Reservations 
where ReservationDate = '2018-1-25'
and ConfirmationNumber IS NOT NULL
and CancelDate IS NULL
group by TableNumber, Customer
order by TableNumber

It produces this result:

enter image description here

Focus on customer Corbosie... above.

Because there is grouping going on, there are PublicNotes data that I'm missing, because some records are being compressed out of the query. I simply want to concatenate the PublicNotes from all records included in the query, including those being aggregated out.

For example, when I query this way:

select ConfirmationNumber, ReservationDate, ifnull(CASE TableNumber WHEN 0 THEN 'UNASSIGNED' ELSE TableNumber END,'UNASSIGNED') AS 'Table', CONCAT(LastName, ', ', FirstName) AS 'Customer', Email, Phone, PublicNotes, SleighSeats + CabSeats AS Seats 
from Reservations 
where ConfirmationNumber in ('092550', '764352', '661800')

...it shows you 3 records that got compressed into 1 record (due to grouping) in the first query:

enter image description here

The 2 PublicNotes circled in red are missing because they got aggregated out. How can I maintain the grouping of my first query, while concatenating in the missing PublicNotes with their grouped record?

Upvotes: 0

Views: 108

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I think you want group_concat():

select ConfirmationNumber, ReservationDate,
       (CASE TableNumber WHEN 0 THEN 'UNASSIGNED' ELSE TableNumber END) AS "Table",
       CONCAT(LastName, ', ', FirstName) AS Customer,
       Email, Phone,
       GROUP_CONCAT(PublicNotes, '|'), 
       (SUM(SleighSeats) + SUM(CabSeats)) AS Seats
from Reservations 
where ReservationDate = '2018-1-25' and
      ConfirmationNumber IS NOT NULL and
      CancelDate IS NULL
group by ConfirmationNumber, ReservationDate, TableNumber, Customer, email, phone
order by TableNumber;

Notes:

  • I don't think TableNumber can be NULL, based on the logic, so I removed the IFNULL(). If it can be, then add it back in (or use COALESCE().
  • I added the additional unaggregated columns to the GROUP BY. This is a good habit for you to use.
  • The separator for the different notes is a vertical bar. You can choose whatever you like.

Upvotes: 1

Related Questions