- 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?
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
Question 11 Result
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.