The code below produce results in Fig. 1 . Please produce code that will give th
ID: 3880845 • Letter: T
Question
The code below produce results in Fig. 1. Please produce code that will give the results in Fig. 2. Fig. Therefore Fig.2 shows which shipper shipped the least number of orders per category.
Note: DISTINCT doesn't work
SQL Code:
SELECT CategoryName, CompanyName, MIN(OrderCount) AS MinOrdersShipped
FROM
(
SELECT
C.CategoryName, S.CompanyName
,O.ShipVia
,COUNT(O.OrderID) AS 'OrderCount'
FROM Orders O
INNER JOIN Shippers S
ON O.ShipVia = S.ShipperID
INNER JOIN [Order Details] D
ON O.OrderID = D.OrderID
INNER JOIN Products P
ON D.ProductID = P.ProductID
INNER JOIN Categories C
ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName,S.CompanyName, O.ShipVia
) AS SHIPCATCOUNT
GROUP BY CategoryName, CompanyName
ORDER BY CategoryName
Fig. 1 1 Beverages Federal Shipping 118 2 Beverages Speedy Express 122 3 Beverages United Package 164 4Condiments Federal Shipping 63 5 Condiments Speedy Express 72 6 Condiments United Package 81 7 Confections Federal Shipping 101 8 Confections Speedy Express 100 9 Confections United Package 133 0 Dairy Products Federal Shipping 108 1 Dairy Products Speedy Express 115 12 Dairy Products United Package 143 13 Grains/Cereals Federal Shipping 56 14 Grains/Cereals Speedy Express 59 15 Grains/Cereals United Package 81 16 Meat/Poultry Federal Shipping 57 7 Meat/Poultry Speedy Express 44 8 Meat/Poultry Uhited Package 72 19 Produce 20 Produce 21 Produce 22 Seafoo Federal Shipping 102 23 Seafood 24 Seafood Federal Shipping 40 Speedy Express 39 United Package 57 Speedy Express 95 United Package 133 Fig. 2 MinOrders CategoryName Shipper Beverages Federal Shipping 118 Condiments Federal Shipping 63 Confections Speedy Express 100 Dairy Products Federal Shipping 108 Grains/Cereals Federal Shipping 56 Meat/PoultrySpeedy Express 44 Produce Seafood Speedy Express 39 Speedy Express 95Explanation / Answer
Hi,
You can use rank in such cases. Please use below query and let me know if you still face difficulty-
select CategoryName, CompanyName,MinOrdersShipped from( select CategoryName, CompanyName,MinOrdersShipped , rank() over( partition by CategoryName, CompanyName order by MinOrdersShipped) as rnk from(SELECT CategoryName, CompanyName, MIN(OrderCount) AS MinOrdersShipped
FROM
(
SELECT
C.CategoryName, S.CompanyName
,O.ShipVia
,COUNT(O.OrderID) AS 'OrderCount'
FROM Orders O
INNER JOIN Shippers S
ON O.ShipVia = S.ShipperID
INNER JOIN [Order Details] D
ON O.OrderID = D.OrderID
INNER JOIN Products P
ON D.ProductID = P.ProductID
INNER JOIN Categories C
ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName,S.CompanyName, O.ShipVia
) AS SHIPCATCOUNT
GROUP BY CategoryName, CompanyName
ORDER BY CategoryName) a
where a.rnk=1;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.