title: SQL - Subquery in Aggregate Function
body: <p>I'm using the northwind database to refresh my SQL skills by creating some more or less complex queries. Unfortunately I could not find a solution for my last use case:
  "Get the sum of the five greatest orders for every category in year 1997."</p>
  
  <p>The tables involved are:</p>
  
  <pre><code>Orders(OrderId, OrderDate)
  Order Details(OrderId, ProductId, Quantity, UnitPrice)
  Products(ProductId, CategoryId)
  Categories(CategoryId, CategoryName)
  </code></pre>
  
  <p>I have tried the following query</p>
  
  <pre><code>SELECT c.CategoryName, SUM(
    (SELECT TOP 5 od2.UnitPrice*od2.Quantity 
     FROM [Order Details] od2, Products p2
     WHERE od2.ProductID = p2.ProductID
     AND c.CategoryID = p2.CategoryID
     ORDER BY 1 DESC))
  FROM [Order Details] od, Products p, Categories c, Orders o 
  WHERE od.ProductID = p. ProductID
  AND p.CategoryID = c.CategoryID
  AND od.OrderID = o.OrderID
  AND YEAR(o.OrderDate) = 1997
  GROUP BY c.CategoryName
  </code></pre>
  
  <p>Well... It turned out that subqueries are not allowed in aggregate functions. I've read other posts about this issue but could not find a solution for my specific use case. Hope you can help me out...</p>