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:

Data:

Table: order_return

Structure:

Data:

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.