How to aggregate results from many rows into a single row mysql

Question

I have an Orders table, with OrderId as PK. I also have an Order_Products table with OrderId as FK. Each Order_Product row has columns like Product_Name, Product_Status,Product_Delivery_Date, etc. The tables are joined in one-to-many relationship on OrderId.

I am trying to design a query which will consolidate the two tables such that I get a row for each OrderId, but the query must have a Description column which will have the Oder_Name values of all Order_Product rows concatenated; I also need to have the aggregate Product_Status calculate using some logic like (if one of the products is 'NotCompleted', then the aggregate Product_Status will be 'NotCompleted'. Finally I would like to have the OrderDeliveryDate calculate such that it will be the MIN value of all Order_Product Product_Delivery_Date values.

I am using MySql. Can you please give me some advice as to how to achieve these three tasks?

Example:

Orders Table:

OrderId CustomerName

1001 CustomerX

OrderProducts Table:

Id OrderId ProductDescription ProductDeliveryDate ProductStatus
20 1001 ProductA 2015-01-30 NotCompleted
21 1001 ProductB 2015-01-15 Completed
24 1001 ProductC 2015-02-13 NotCompleted

Desired Query Output:

OrderId CustomerName Description OrderDeliveryDate OrderStatus
'1001' 'CustomerX' 'ProductA,ProductB,ProductC' '2015-01-15' 'NotCompleted'

You should edit your question and add sample data and desired results. — Gordon Linoff, Jan 04 '15 at 19:15

Gordon Linoff · Accepted Answer · 2015-01-05T02:03:44.867

1

From what you describe, a query such a the following may do what you want:

select o.*, op.status, op.orderdeliverydate, op.description
from orders o join
     (select OrderId, group_concat(op.order_name) as description,
             (case when sum(Product_Status = 'Not Completed') > 0 then 'Not Completed'
                   else 'Completed'
              end) as Status,
             min(Product_Delivery_date) as OrderDeliveryDate
      from Order_Product
      group by OrderId
     ) op
     on o.OrderId = p.OrderId;

edited Jan 05 '15 at 02:03

answered Jan 04 '15 at 19:18

Gordon Linoff

1,242,037
58
646
786

thanks for your response; it seems to be solving two out of three of my problems; any ideas on how to tackle the description issue? – user1012598 Jan 04 '15 at 21:06
@user1012598 . . . That would appear to require `group_concat()`. – Gordon Linoff Jan 05 '15 at 02:03
Thanks Gordon. Please see an example of the requirement above. – user1012598 Jan 05 '15 at 18:11

How to aggregate results from many rows into a single row mysql

Orders Table:

OrderProducts Table:

Desired Query Output:

1 Answers1