Sql server request does not work as a subquery

Question

Why does this query not work?

Select temp.CompanyName from

(
    SELECT  c.CompanyName, o.OrderID, 
            YEAR(o.OrderDate)  As YEAR, 
            Sum(od.UnitPrice * od.Quantity) from Orders o 
        INNER JOIN [Order Details] od 
            ON o.OrderID = od.OrderID
        INNER JOIN Customers c
            On c.CustomerID = o.CustomerID
                GROUP BY o.OrderId,c.CompanyName, YEAR(o.OrderDate)

) As temp;

It uses the Northwind database. If I run it without creating a temporary view, then if I run a query contained only in parentheses, it works just fine.

Answer 1

at first glance I would say because your Sum () does not have a column alias

Answer 2

try the following:

Select CompanyName from
(
    SELECT  c.CompanyName, o.OrderID, 
            YEAR(o.OrderDate)  As YEAR, 
            Sum(od.UnitPrice * od.Quantity) as price from Orders o 
        INNER JOIN [Order Details] od 
            ON o.OrderID = od.OrderID
        INNER JOIN Customers c
            On c.CustomerID = o.CustomerID
                GROUP BY o.OrderId,c.CompanyName, YEAR(o.OrderDate)

)  temp