﻿ SQL Challenges-1: Sale quantity of each quarter for a product - w3resource

# SQL Challenges-1: Sale quantity of each quarter for a product

## SQL Challenges-1: Exercise-41 with Solution

From the following table write a SQL query to make a report such that there is a product id column and a sale quantity column for each quarter. Return product ID and sale quantity of each quarter.

Input:

Table: sale

Structure:

FieldTypeNullKeyDefaultExtra
product_idint(11)NOPRI
sale_qtyint(11)YES
qtr_novarchar(25)NOPRI

Data:

product_idsale_qtyqtr_no
115000qtr1
110000qtr2
220000qtr1
212000qtr2
320000qtr1
315000qtr2
323000qtr3
322000qtr4
425000qtr2
418000qtr4

Sample Solution:

SQL Code(MySQL):

``````CREATE TABLE sale (product_id int not null, sale_qty int, qtr_no varchar(25) not null,
PRIMARY KEY(product_id,qtr_no));
INSERT INTO sale VALUES (3,20000,'qtr1');
INSERT INTO sale VALUES (2,12000,'qtr2');
INSERT INTO sale VALUES (3,23000,'qtr3');
INSERT INTO sale VALUES (1,10000,'qtr2');
INSERT INTO sale VALUES (3,15000,'qtr2');
INSERT INTO sale VALUES (1,15000,'qtr1');
INSERT INTO sale VALUES (4,25000,'qtr2');
INSERT INTO sale VALUES (2,20000,'qtr1');
INSERT INTO sale VALUES (4,18000,'qtr4');
INSERT INTO sale VALUES (3,22000,'qtr4');

SELECT
product_id,
MIN(IF(qtr_no = 'qtr1', sale_qty, null)) AS qtr1_sale,
MIN(IF(qtr_no = 'qtr2', sale_qty, null)) AS qtr2_sale,
MIN(IF(qtr_no = 'qtr3', sale_qty, null)) AS qtr3_sale,
MIN(IF(qtr_no = 'qtr4', sale_qty, null)) AS qtr4_sale
FROM sale
GROUP BY product_id
ORDER BY product_id;
```
```

Sample Output:

```product_id	qtr1_sale	qtr2_sale	qtr3_sale	qtr4_sale
1	15000	10000	NULL	NULL
2	20000	12000	NULL	NULL
3	20000	15000	23000	22000
4	NULL	25000	NULL	18000
```

SQL Code Editor: