w3resource

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

SQL Challenges-1: Exercise-43 with Solution

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:

FieldTypeNullKeyDefaultExtra
order_idint(11)NOPRI
com_namevarchar(25)YES
ord_qtyint(11)YES
ord_statvarchar(25)YES
stat_datedateYES

Data:

order_idcom_nameord_qtyord_statstat_date
151MMS INC500Booked2020-08-15
152BCT LTD300Cancelled2020-08-15
153MMS INC400Cancelled2020-08-26
154XYZ COR500Booked2020-08-15
155MMS INC500Cancelled2020-10-11
156BWD PRO LTD250Cancelled2020-11-15
157BCT LTD600Booked2020-10-07
158MMS INC300Booked2020-12-11
159XYZ COR300Booked2020-08-26
160BCT LTD400Booked2020-11-15

Table: order_return

Structure:

FieldTypeNullKeyDefaultExtra
order_idint(11)NOMUL
return_datedateYES

Data:

order_idreturn_date
1532020-10-12
1542020-11-07
1562020-12-05
1592020-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.



Follow us on Facebook and Twitter for latest update.