SQL Question 11

Problem

  • USE Northwind
  • Rank products with in their category by total dollar amount sold Dollar amount sold is calculated as (quantity* unitprice) in the order details table. Don’t worry about ties in total sold?

Code

SELECT c.CategoryName , p.ProductName , SUM(od.quantity*od.unitprice) 'Total sold'
, Rank() OVER (partition by c.categoryname order by SUM(od.quantity*od.unitprice) desc) 'Row'
FROM [Order Details] od JOIN Products p ON od.ProductID = p.ProductID
JOIN Categories c ON c.CategoryID = P.CategoryID
GROUP BY c.CategoryName, p.ProductName
ORDER BY c.CategoryName, row

Result

Question 11 Result

Question 11 Result

Notes

This problem is using the RANK function to order the results by 'Total Sold' with a PARTITION BY over CategoryName. Notice that the RANK function is its own column using the PARTITION BY with a selected column followed by an ORDER BY statement. The RANK column is aliased as 'Row'. Also, the ORDER BY clause is using the aliased 'Row' column.

No Comments Yet.

Leave a comment