Parin - 8 months ago 21

MySQL Question

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
```