SQL Challenges-1: Order status report for each month for each company

SQL Challenges-1: Exercise-42 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 booked and their total order quantity. Return month, name of the company, number of orders issued, number of booked orders, total order quantity and total booked orders quantity.

Input:

Table: order_stat

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');


SELECT
    DATE_FORMAT(stat_date, "%Y-%m") AS "month year",
    com_name,
    SUM(CASE WHEN ord_stat = 'Booked' THEN 1
            WHEN ord_stat = 'Cancelled' THEN 1
            ELSE 0 END) AS no_of_orders,
    SUM(CASE WHEN ord_stat = 'Booked' THEN 1 ELSE 0 END) AS booked_orders,
    SUM(ord_qty) AS total_order_qty,
    SUM(CASE WHEN ord_stat = 'Booked' THEN ord_qty ELSE 0 END) AS no_of_booked_qty
FROM order_stat
GROUP BY com_name, DATE_FORMAT(stat_date, "%Y-%m");

Sample Output:

month year	com_name	no_of_orders	booked_orders	total_order_qty	no_of_booked_qty
2020-08		MMS INC		2		1		900				500
2020-08		BCT LTD		1		0		300				0
2020-08		XYZ COR		2		2		800				800
2020-10		MMS INC		1		0		500				0
2020-11		BWD PRO LTD	1		0		250				0
2020-10		BCT LTD		1		1		600				600
2020-12		MMS INC		1		1		300				300
2020-11		BCT LTD		1		1		400				400

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Sale quantity of each quarter for a product.
Next: Order status report for each month for each company to find booked and cancelled order number and quantity.