SQL - Subquery in Aggregate Function

Question

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…

Accepted Answer

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

SQL - Subquery in Aggregate Function

Solution

Problem