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

Last update on December 20 2024 11:39:32 (UTC/GMT +8 hours)

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:

Field	Type	Null	Key
product_id	int(11)	NO	PRI
sale_qty	int(11)	YES
qtr_no	varchar(25)	NO	PRI

Data:

product_id	sale_qty	qtr_no
1	15000	qtr1
1	10000	qtr2
2	20000	qtr1
2	12000	qtr2
3	20000	qtr1
3	15000	qtr2
3	23000	qtr3
3	22000	qtr4
4	25000	qtr2
4	18000	qtr4

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:

Contribute your code and comments through Disqus.

Previous: Writers who rated more than one topics on the same date.
Next: Order status report for each month for each company.