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:


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.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8