SELECT p.ProductID, p.ProductCategory, SUM(pd.Quantity) AS Quantity_by_Category 
FROM Purchase_Details pd 
INNER JOIN Products p
ON pd.ProductID = p.ProductID
GROUP BY pd.ProductID
ORDER BY p.ProductID;

enter image description here

CodePudding user response：

Can be done with aggregate function MAX() using your query as a subquery:

SELECT MAX(Quantity_by_Category)
FROM (your_query) t1

CodePudding user response：

you can put order by to Quantity_by_Category desc