0

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

The Datas

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

The Result What I wanted

2
1
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
2
  • hai mrs Indiri, thank you for you response. that query it's working nicely with some little editing. But, can I do the looping based on the child's name of the consumer ?? when i try to change "sum (case when parent_customer IN ('Customer B', 'Customer C') and child_customer IN ('ha', 'hi', 'hu', 'he', 'ho') then qty else 0 end) as tags "(using only one line). The result only gives one column. Thank you very much
    – epm.007
    Jul 3 '18 at 3:40
  • Each case statement is it's own column. "sum (case when parent_customer IN ('Customer B', 'Customer C') and child_customer IN ('ha', 'hi', 'hu', 'he', 'ho') then qty else 0 end) as tags " is the same as sum(qty) because you are including all permutations in a single column. If you only want to break it up by the child and don't care which parent then you could reduce it to 5 columns by removing the parent_customer='B' or combine just that part to "sum (case when parent_customer IN ('Customer B', 'Customer C') and child_customer ='ha' then qty else 0 end) as tags_ha ".
    – indiri
    Jul 3 '18 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.