Parin Parin - 5 months ago 12
MySQL Question

MySQL query to show combinations of output

I have to write a mysql query to find total sales on particular days. I am able to find it, but unable to show combined results.

The table looks like:

Date, Order_id, Product_id, Quantity
'01-JUL-11',O1,P1,5
'01-JUL-11',O2,P2,2
'01-JUL-11',O3,P3,10
'01-JUL-11',O4,P1,10
'02-JUL-11',O5,P3,5
'02-JUL-11',O6,P4,6
'02-JUL-11',O7,P1,2
'02-JUL-11',O8,P5,1
'02-JUL-11',O9,P6,2
'02-JUL-11',O10,P2,4


Desired Output:

Product_id Total Sales on Day '02-JUL-11' Total Sales on Day '02-JUL-11'
P1 15 2
P2 2 4
P3 10 5
P4 0 6
P5 0 1
P6 0 2


What I have tried is:

Select distinct product_id
from orders;


Output:

P1
P2
P3
P4
P5
P6

Select product_id, sum(quantity) from orders
where order_day = '11-07-01'
group by product_id


OUTPUT:

P1 15
P2 2
P3 10


Select product_id, sum(quantity) from orders
where order_day = '11-07-02'
group by product_id


OUTPUT:

P1 2
P2 4
P3 5
P4 6
P5 1
P6 2


This gives me the desired result but I want to somehow combine the columns.

Query that worked for me: (However puts null for 0)

Select X.product_id, X.s, Y.t from
(SELECT A.product_id as product_id, B.s as s FROM
(Select distinct product_id
from orders) A
LEFT JOIN
(Select product_id, sum(quantity) as s from orders
where order_day = '11-07-01'
group by product_id) B
ON A.product_id = B.product_id) X
Left join
(Select product_id, sum(quantity) as t from orders
where order_day = '11-07-02'
group by product_id) Y
on X.product_id = Y.product_id;

Answer

If you need only two days summarized in your report, then a simple (non dynamic) pivot query should do the trick:

SELECT product_id,
    SUM(CASE WHEN order_day = '11-07-01' THEN quantity ELSE 0 END) AS `Total Sales on Day '01-JUL-11'`,
    SUM(CASE WHEN order_day = '11-07-02' THEN quantity ELSE 0 END) AS `Total Sales on Day '02-JUL-11'`
FROM orders
WHERE order_day = '11-07-01' OR order_day = '11-07-02'
GROUP BY product_id
Comments