The SQL Code below pull data in Fig. 1 . I want to pull the minimum OrderCount f
ID: 3880271 • Letter: T
Question
The SQL Code below pull data in Fig. 1. I want to pull the minimum OrderCount from each CategoryName. Please write SQL Code for Fig. The code should give results in Fig 2. The minimum is derived from COUNT(O.OrderID) AS 'OrderCount'. The OrderCount is a calculation and not in located in any table. I need to take the MIN of the OrderCount and display results in Fig. 2. Therefore the issue I'm having is finding a way to use the alias to filter the MIN.
Note: OrderCount is alias.
SQL Code:
------------------------------------------------------------------
SELECT
C.CategoryName
,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, O.ShipVia
ORDER BY C.CategoryName, O.ShipVia
----------------------------------------------------------------------
Results:
Fig. 1 CategoryName Shipia OrderCount Beverages Beverages 2 Beverages 3 Condiments 1 Condiments 2 Condiments 3 Confections 1 Confections 2 Confections 3 122 164 118 72 81 63 100 133 101 Fig. 2 CategoryName MinOrders Shipped Beverages 118 Condiments 63 Confections 100Explanation / Answer
// Inorder to get the minimum order count, we need to consider the entire given query output as a table, say SHIPCATCOUNT, and take minimum order count from that table.
Query:-
SELECT CategoryName,MIN(OrderCount) AS MinOrdersShipped
FROM
(
SELECT
C.CategoryName
,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, O.ShipVia
ORDER BY C.CategoryName, O.ShipVia
) AS SHIPCATCOUNT
GROUP BY CategoryName
ORDER BY CategoryName
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.