MySQL – How to Split and Group Quantities by Child Customer Order

MySQL

I have some data like in picture below on and need to perform some query.

And then I try to produce result like the picture below, but I have trouble and don't know how to split qty by child customer order.

How to get results like picture 2?

This is the query I have tried in order to obtain the result I want:

SELECT item
    ,no_po
    ,sum(qty) as total
    ,parent_customer
    ,child_customer
    ,qty 
FROM `detail_order` 
WHERE received_date='2018-06-27' 
group by item 
ORDER BY item asc

Best Answer

select item, no_po, sum(qty) qty_total,
    sum(case when parent_customer='Customer B' and child_customer='ha' then qty else 0 end) as customer_b_ha,
    sum(case when parent_customer='Customer B' and child_customer='he' then qty else 0 end as customer_b_he,
    sum(case when parent_customer='Customer B' and child_customer='hi' then qty else 0 end as customer_b_hi,
    sum(case when parent_customer='Customer B' and child_customer='ho' then qty else 0 end as customer_b_ho,
    sum(case when parent_customer='Customer B' and child_customer='hu' then qty else 0 end as customer_b_hu,
    sum(case when parent_customer='Customer C' and child_customer='ha' then qty else 0 end as customer_c_ha,
    sum(case when parent_customer='Customer C' and child_customer='he' then qty else 0 end as customer_c_he,
    sum(case when parent_customer='Customer C' and child_customer='hi' then qty else 0 end as customer_c_hi,
    sum(case when parent_customer='Customer C' and child_customer='ho' then qty else 0 end as customer_c_ho,
    sum(case when parent_customer='Customer C' and child_customer='hu' then qty else 0 end as customer_c_hu
from detail_order
where received_date='2018-06-27' 
group by item, no_po
order by item asc, no_po

Related Solutions

Mysql – How to get a list/result of best selling items

Assuming SQL Server, I might use:

SELECT TOP(5) ProductID, SUM(Quantity) AS TotalQuantity
FROM order_items
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC;

This returns the top 5 best-selling Products.

Since SUM(Quantity) is also included in the SELECT clause, you can reference its alias in ORDER BY instead of repeating the expression:

...
ORDER BY TotalQuantity DESC;

Mysql – How to sum the buy order and sell order in one row per each ticker

below should help you :

CREATE TABLE dbo.Trading
        ([Ticker] int, [iOrder] int, [Price] int, [Amount] int)
    ;

INSERT INTO dbo.Trading
    ([Ticker], [iOrder], [Price], [Amount])
VALUES
    (1, 1, 90, 180),
    (1, 1, 89.5, 179),
    (1, -1, 91, 91),
    (2, 1, 5, 20),
    (2, -1, 4, 16),
    (2, -1, 3, 9)
;


SELECT Ticker
    ,sum(CASE 
            WHEN iorder = 1
                THEN Amount
            ELSE 0
            END) AS BuyAmount
    ,sum(CASE 
            WHEN iorder = - 1
                THEN amount
            ELSE 0
            END) AS SellAmount
FROM dbo.Trading
GROUP BY Ticker

Best Answer

Related Solutions

Mysql – How to get a list/result of best selling items

Mysql – How to sum the buy order and sell order in one row per each ticker

Related Question