Sql Server query not working as a subquery

Question

Why is this query not working?

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 Northwind database. If I run it without creating a temporary view i.e if I run the query just contained within round brackets it runs just fine.

Answer 1

at the first look i'd say because your Sum() doesn't have a column alias

Answer 2

try this :

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