SQL - Subquery in Aggregate Function

Thomas  picture Thomas · May 1, 2013 · Viewed 83.3k times · Source

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."

The tables involved are:

Orders(OrderId, OrderDate)
Order Details(OrderId, ProductId, Quantity, UnitPrice)
Products(ProductId, CategoryId)
Categories(CategoryId, CategoryName)

I have tried the following query

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

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...

Answer

Gordon Linoff picture Gordon Linoff · May 1, 2013

Subqueries are not generally allowed in aggregate functions. Instead, move the aggregate inside the subquery. In this case, you'll need an extra level of subquery because of the top 5:

SELECT c.CategoryName,
  (select sum(val)
   from (SELECT TOP 5 od2.UnitPrice*od2.Quantity as val
         FROM [Order Details] od2, Products p2
         WHERE od2.ProductID = p2.ProductID
         AND c.CategoryID = p2.CategoryID
         ORDER BY 1 DESC
        ) t
  )
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, c.CategoryId