CODEWITHSUNDEEP
sql-server-2008
TCM
TCM

Reputation: 16900

Sql Server query not working as a subquery

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.

Upvotes: 3

Views: 78

Answers (2)

Pranay Rana
Pranay Rana

Reputation: 176906

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

Upvotes: 1

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

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

Upvotes: 5

Related Questions