The SQL Code below pull data in Fig. 1 . I want to pull the minimum OrderCount f

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 derive from COUNT(O.OrderID) AS 'OrderCount'.

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 100

Explanation / Answer

Query:

SELECT C.CategoryName, min(O.OrderCount) AS 'MinOrdersShipped' FROM Orders O
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 having min(O.OrderCount);

(or)

select CategoryName, min(OrderCount) from Categories, Order_Details
group by CategoryName having min(OrderCount);

Explanation:

It retrieve the categoryname, ordercount with minimum value from the tables category and order_details acceordingly by grouping with the all of the categorynames with having the minimum value from it.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.