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:

Data:

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.