SQL Challenges-1: Order status report for each month for each company to find booked and cancelled order number and quantity

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

From the following table write a SQL query to find for each month and company, the number of orders issued and their total quantity, the number of orders cancelled and their total quantity. Return month, name of the company, number of orders booked, number of booked quantity, number of cancelled order, and number of cancelled quantity.

Input:

Table: order_stat

Structure:

Field	Type	Null	Key
order_id	int(11)	NO	PRI
com_name	varchar(25)	YES
ord_qty	int(11)	YES
ord_stat	varchar(25)	YES
stat_date	date	YES

Data:

order_id	com_name	ord_qty	ord_stat	stat_date
151	MMS INC	500	Booked	2020-08-15
152	BCT LTD	300	Cancelled	2020-08-15
153	MMS INC	400	Cancelled	2020-08-26
154	XYZ COR	500	Booked	2020-08-15
155	MMS INC	500	Cancelled	2020-10-11
156	BWD PRO LTD	250	Cancelled	2020-11-15
157	BCT LTD	600	Booked	2020-10-07
158	MMS INC	300	Booked	2020-12-11
159	XYZ COR	300	Booked	2020-08-26
160	BCT LTD	400	Booked	2020-11-15

Table: order_return

Structure:

Field	Type	Null	Key	Default	Extra
order_id	int(11)	NO	MUL
return_date	date	YES

Data:

order_id	return_date
153	2020-10-12
154	2020-11-07
156	2020-12-05
159	2020-09-17

Sample Solution:

SQL Code(MySQL):

CREATE TABLE order_stat (order_id int not null unique, com_name varchar(25), ord_qty int, ord_stat varchar(25), stat_date date);

INSERT INTO order_stat VALUES (151, 'MMS INC'		,500,	'Booked',		'2020-08-15');
INSERT INTO order_stat VALUES (152, 'BCT LTD'		,300,	'Cancelled',	'2020-08-15');
INSERT INTO order_stat VALUES (153, 'MMS INC'		,400,	'Cancelled',	'2020-08-26');
INSERT INTO order_stat VALUES (154, 'XYZ COR'		,500,	'Booked',		'2020-08-15');
INSERT INTO order_stat VALUES (155, 'MMS INC'		,500,	'Cancelled',	'2020-10-11');
INSERT INTO order_stat VALUES (156, 'BWD PRO LTD'	,250,	'Cancelled',	'2020-11-15');
INSERT INTO order_stat VALUES (157, 'BCT LTD'		,600,	'Booked',		'2020-10-07');
INSERT INTO order_stat VALUES (158, 'MMS INC'		,300,	'Booked',		'2020-12-11');
INSERT INTO order_stat VALUES (159, 'XYZ COR'		,300,	'Booked',		'2020-08-26');
INSERT INTO order_stat VALUES (160, 'BCT LTD'		,400,	'Booked',		'2020-11-15');
	 
	 
	 
CREATE TABLE order_return (order_id int not null, return_date date ,
FOREIGN KEY(order_id) REFERENCES order_stat(order_id));	

INSERT INTO order_return VALUES (153, '2020-10-12');
INSERT INTO order_return VALUES (154, '2020-11-07');
INSERT INTO order_return VALUES (156, '2020-12-05');
INSERT INTO order_return VALUES (159, '2020-09-17');



select left(stat_date, 7) as month, com_name, 
sum(case when ord_stat='Booked' then 1 else 0 end) as booked_count, 
sum(case when ord_stat='Booked' then ord_qty else 0 end) as booked_qty, 
sum(case when ord_stat='Cancelled' then 1 else 0 end) as cancelled_count, 
sum(case when ord_stat='Cancelled' then ord_qty else 0 end) as cancelled_qty
from
((select *
from order_stat)
union
(select a.order_id, a.com_name, 'orders back', a.ord_qty, b.return_date
from order_stat as a
join order_return b
on a.order_id = b.order_id)) as c
group by month, com_name
having booked_count > 0 or booked_qty > 0 or cancelled_count > 0 or cancelled_qty > 0

Sample Output:

month  |com_name   |booked_count|booked_qty|cancelled_count|cancelled_qty|
-------|-----------|------------|----------|---------------|-------------|
2020-08|BCT LTD    |           0|       0.0|              1|        300.0|
2020-08|MMS INC    |           1|     500.0|              1|        400.0|
2020-08|XYZ COR    |           2|     800.0|              0|          0.0|
2020-10|BCT LTD    |           1|     600.0|              0|          0.0|
2020-10|MMS INC    |           0|       0.0|              1|        500.0|
2020-11|BCT LTD    |           1|     400.0|              0|          0.0|
2020-11|BWD PRO LTD|           0|       0.0|              1|        250.0|
2020-12|MMS INC    |           1|     300.0|              0|          0.0|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Order status report for each month for each company.
Next: Average Selling Price.