w3resource

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:

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

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.



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