﻿ 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:

﻿

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