w3resource

SQL Challenges-1: Find the highest daily total order for an item

SQL Challenges-1: Exercise-63 with Solution

From the following table write a query in SQL to find the highest daily total order for an item between 2019-07-01 to 2019-12-31. Return item description, order date and the total order quantity.

Table: orders

Structure:

FieldTypeNullKeyDefaultExtra
ORDER_IDintNOPRI
CUSTOMER_IDintNO
ITEM_DESCvarchar(30)NO
ORDER_DATEdateNO
order_qtyintNO

Data:

ORDER_IDCUSTOMER_IDITEM_DESCORDER_DATEorder_qty
1012109juice2019-07-2150
1022139chocolate2019-05-1740
1032120juice2019-05-1740
1042108cookies2019-05-1750
1052130juice2019-10-1845
1062103cake2019-07-2135
1072122cookies2019-12-1740
1082125cake2019-12-1738
1092139cake2019-07-2140
1102141cookies2019-05-1760
1112116cake2019-10-1845
1122128cake2019-10-1838
1132146chocolate2019-10-1855
1142119cookies2019-10-1830
1152142cake2019-03-0526
1162122cake2019-03-0559
1172128chocolate2019-06-1945
1182112cookies2019-06-1928
1192149cookies2019-10-1849
1202100cookies2020-03-1476
1212130juice2020-03-1420
1222103juice2019-07-2127
1232112cookies2019-06-1952
1242102cake2019-07-2114
1252120chocolate2019-07-2185
1262109cake2019-06-1918
1272101juice2019-10-1864
1282138juice2019-06-1955
1292100juice2019-07-2145
1302129juice2019-10-1835
1312123juice2019-10-1825
1322104chocolate2019-10-1863
1332110cake2019-12-1738
1342143cake2019-06-1927
1352130juice2019-05-1730
1362128juice2019-10-1840
1372133cookies2019-06-1951
1382150cookies2019-06-1943
1392120juice2019-10-1855
1402109cake2019-10-1844
1412110cake2019-12-1750
1422140juice2019-03-0565
1432112cookies2019-10-1846
1442117chocolate2019-06-1925
1452137cookies2019-07-2149
1462130cake2019-12-1740
1472133cake2019-07-2130
1482143juice2019-03-0560
1492111chocolate2019-07-2174
1502150cookies2019-10-1875
1512131cake2019-07-2145
1522140chocolate2019-05-1740
1532147cookies2019-07-2165
1542119chocolate2019-05-1760
1552116juice2019-12-1740
1562141juice2019-12-1750
1572143cake2019-05-1755
1582105cake2019-07-2170
1592149chocolate2019-03-0550
1602117cookies2019-07-2161
1612150cookies2019-07-2150
1622134cake2019-03-0555
1632133cookies2019-06-1954
1642127juice2019-06-1955
1652101juice2019-06-1945
1662137chocolate2019-12-1740
1672113chocolate2019-06-1930
1682141cake2019-06-1960
1692112chocolate2019-12-1725
1702118juice2019-10-1870
1712111juice2019-06-1960
1722146chocolate2019-12-1720
1732148cookies2020-03-1455
1742100cookies2019-12-1740
1752105cookies2019-03-0530
1762129juice2019-10-1840
1772121juice2019-05-1730
1782117cake2019-07-2140
1792133juice2019-07-2120
1802124cake2019-07-2130
1812145cake2019-12-1725
1822105cookies2019-03-0520
1832131juice2019-03-0550
1842114chocolate2019-10-1856
1852120juice2019-12-1755
1862130juice2019-12-1746
1872141chocolate2019-03-0554
1882147cake2020-03-1440
1892118juice2019-05-1754
1902136chocolate2019-07-2158
1912132cake2019-12-1750
1922137chocolate2019-07-2153
1932107cake2019-10-1835
1942111chocolate2019-05-1750
1952100cake2019-03-0555
1962106juice2019-07-2147
1972114cookies2019-06-1955
1982110cake2019-06-1975
1992130juice2019-05-1755
2002117cake2019-12-1748

Sample Solution:

SQL Code(MySQL):

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
ORDER_ID            INTEGER(5) NOT NULL,
CUSTOMER_ID 	            INTEGER(4) NOT NULL,
ITEM_DESC 	            varchar(30) NOT NULL,
ORDER_DATE               date NOT NULL,
order_qty integer(4) not null,
PRIMARY KEY (ORDER_ID)
);

insert into orders values(101	,2109	,'juice		','2019-07-21',	50);
insert into orders values(102	,2139	,'chocolate	','2019-05-17',	40);
insert into orders values(103	,2120	,'juice		','2019-05-17',	40);
insert into orders values(104	,2108	,'cookies		','2019-05-17',	50);
insert into orders values(105	,2130	,'juice		','2019-10-18',	45);
insert into orders values(106	,2103	,'cake		','2019-07-21',	35);
insert into orders values(107	,2122	,'cookies		','2019-12-17',	40);
insert into orders values(108	,2125	,'cake		','2019-12-17',	38);
insert into orders values(109	,2139	,'cake		','2019-07-21',	40);
insert into orders values(110	,2141	,'cookies		','2019-05-17',	60);
insert into orders values(111	,2116	,'cake		','2019-10-18',	45);
insert into orders values(112	,2128	,'cake		','2019-10-18',	38);
insert into orders values(113	,2146	,'chocolate	','2019-10-18',	55);
insert into orders values(114	,2119	,'cookies		','2019-10-18',	30);
insert into orders values(115	,2142	,'cake		','2019-03-05',	26);
insert into orders values(116	,2122	,'cake		','2019-03-05',	59);
insert into orders values(117	,2128	,'chocolate	','2019-06-19',	45);
insert into orders values(118	,2112	,'cookies		','2019-06-19',	28);
insert into orders values(119	,2149	,'cookies		','2019-10-18',	49);
insert into orders values(120	,2100	,'cookies		','2020-03-14',	76);
insert into orders values(121	,2130	,'juice		','2020-03-14',	20);
insert into orders values(122	,2103	,'juice		','2019-07-21',	27);
insert into orders values(123	,2112	,'cookies		','2019-06-19',	52);
insert into orders values(124	,2102	,'cake		','2019-07-21',	14);
insert into orders values(125	,2120	,'chocolate	','2019-07-21',	85);
insert into orders values(126	,2109	,'cake		','2019-06-19',	18);
insert into orders values(127	,2101	,'juice		','2019-10-18',	64);
insert into orders values(128	,2138	,'juice		','2019-06-19',	55);
insert into orders values(129	,2100	,'juice		','2019-07-21',	45);
insert into orders values(130	,2129	,'juice		','2019-10-18',	35);
insert into orders values(131	,2123	,'juice		','2019-10-18',	25);
insert into orders values(132	,2104	,'chocolate	','2019-10-18',	63);
insert into orders values(133	,2110	,'cake		','2019-12-17',	38);
insert into orders values(134	,2143	,'cake		','2019-06-19',	27);
insert into orders values(135	,2130	,'juice		','2019-05-17',	30);
insert into orders values(136	,2128	,'juice		','2019-10-18',	40);
insert into orders values(137	,2133	,'cookies		','2019-06-19',	51);
insert into orders values(138	,2150	,'cookies		','2019-06-19',	43);
insert into orders values(139	,2120	,'juice		','2019-10-18',	55);
insert into orders values(140	,2109	,'cake		','2019-10-18',	44);
insert into orders values(141	,2110	,'cake		','2019-12-17',	50);
insert into orders values(142	,2140	,'juice		','2019-03-05',	65);
insert into orders values(143	,2112	,'cookies		','2019-10-18',	46);
insert into orders values(144	,2117	,'chocolate	','2019-06-19',	25);
insert into orders values(145	,2137	,'cookies		','2019-07-21',	49);
insert into orders values(146	,2130	,'cake		','2019-12-17',	40);
insert into orders values(147	,2133	,'cake		','2019-07-21',	30);
insert into orders values(148	,2143	,'juice		','2019-03-05',	60);
insert into orders values(149	,2111	,'chocolate	','2019-07-21',	74);
insert into orders values(150	,2150	,'cookies		','2019-10-18',	75);
insert into orders values(151	,2131	,'cake		','2019-07-21',	45);
insert into orders values(152	,2140	,'chocolate	','2019-05-17',	40);
insert into orders values(153	,2147	,'cookies		','2019-07-21',	65);
insert into orders values(154	,2119	,'chocolate	','2019-05-17',	60);
insert into orders values(155	,2116	,'juice		','2019-12-17',	40);
insert into orders values(156	,2141	,'juice		','2019-12-17',	50);
insert into orders values(157	,2143	,'cake		','2019-05-17',	55);
insert into orders values(158	,2105	,'cake		','2019-07-21',	70);
insert into orders values(159	,2149	,'chocolate	','2019-03-05',	50);
insert into orders values(160	,2117	,'cookies		','2019-07-21',	61);
insert into orders values(161	,2150	,'cookies		','2019-07-21',	50);
insert into orders values(162	,2134	,'cake		','2019-03-05',	55);
insert into orders values(163	,2133	,'cookies		','2019-06-19',	54);
insert into orders values(164	,2127	,'juice		','2019-06-19',	55);
insert into orders values(165	,2101	,'juice		','2019-06-19',	45);
insert into orders values(166	,2137	,'chocolate	','2019-12-17',	40);
insert into orders values(167	,2113	,'chocolate	','2019-06-19',	30);
insert into orders values(168	,2141	,'cake		','2019-06-19',	60);
insert into orders values(169	,2112	,'chocolate	','2019-12-17',	25);
insert into orders values(170	,2118	,'juice		','2019-10-18',	70);
insert into orders values(171	,2111	,'juice		','2019-06-19',	60);
insert into orders values(172	,2146	,'chocolate	','2019-12-17',	20);
insert into orders values(173	,2148	,'cookies		','2020-03-14',	55);
insert into orders values(174	,2100	,'cookies		','2019-12-17',	40);
insert into orders values(175	,2105	,'cookies		','2019-03-05',	30);
insert into orders values(176	,2129	,'juice		','2019-10-18',	40);
insert into orders values(177	,2121	,'juice		','2019-05-17',	30);
insert into orders values(178	,2117	,'cake		','2019-07-21',	40);
insert into orders values(179	,2133	,'juice		','2019-07-21',	20);
insert into orders values(180	,2124	,'cake		','2019-07-21',	30);
insert into orders values(181	,2145	,'cake		','2019-12-17',	25);
insert into orders values(182	,2105	,'cookies		','2019-03-05',	20);
insert into orders values(183	,2131	,'juice		','2019-03-05',	50);
insert into orders values(184	,2114	,'chocolate	','2019-10-18',	56);
insert into orders values(185	,2120	,'juice		','2019-12-17',	55);
insert into orders values(186	,2130	,'juice		','2019-12-17',	46);
insert into orders values(187	,2141	,'chocolate	','2019-03-05',	54);
insert into orders values(188	,2147	,'cake		','2020-03-14',	40);
insert into orders values(189	,2118	,'juice		','2019-05-17',	54);
insert into orders values(190	,2136	,'chocolate	','2019-07-21',	58);
insert into orders values(191	,2132	,'cake		','2019-12-17',	50);
insert into orders values(192	,2137	,'chocolate	','2019-07-21',	53);
insert into orders values(193	,2107	,'cake		','2019-10-18',	35);
insert into orders values(194	,2111	,'chocolate	','2019-05-17',	50);
insert into orders values(195	,2100	,'cake		','2019-03-05',	55);
insert into orders values(196	,2106	,'juice		','2019-07-21',	47);
insert into orders values(197	,2114	,'cookies		','2019-06-19',	55);
insert into orders values(198	,2110	,'cake		','2019-06-19',	75);
insert into orders values(199	,2130	,'juice		','2019-05-17',	55);
insert into orders values(200	,2117	,'cake		','2019-12-17',	48);



SELECT item_desc,order_date,SUM(order_qty) dayorder 
FROM orders
WHERE order_date BETWEEN '2019-07-01' AND '2019-12-31'
GROUP BY item_desc,order_date
ORDER BY dayorder DESC
LIMIT 1;

Sample Output:

item_desc|order_date|daysOrder|
---------+----------+---------+
juice    |2019-10-18|      374|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Find departments with 6 or more employees.
Next: Count the number of patients treated by each doctor.



Follow us on Facebook and Twitter for latest update.