CODEWITHSUNDEEP
sqlsql-server-2008
Patrick
Patrick

Reputation: 41

SQL Server 2008: nested statement

I'm trying to write a statement to calculate a total of invoice, each invoice as multiple products and price. I got:

SELECT SUM((UnitPrice-Discount)*Quantity)
FROM tblOrderDetails 
WHERE OrderID= OrderID 
GROUP BY OrderID ORDER BY OrderID

This is working fine, now I need to add the freight from another table (tblOrder) and this is where I'm stuck, I did:

SELECT OrderID, Freight+
    (SELECT SUM((UnitPrice-Discount)*Quantity)
    FROM tblOrderDetails 
    WHERE OrderID= OrderID 
    GROUP BY OrderID 
    ORDER BY OrderID)
FROM tblOrders

But I keep getting the error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Any help would be appreciated.

Upvotes: 4

Views: 189

Answers (4)

user166390
user166390

Reputation:

It means just what is says :-)

The error occurs because only the ordering of the final result-set (that which is returned to the client) is guaranteed when using ORDER BY. All intermediate result sets are just that -- sets of records -- the order of the items in the set can vary. ORDER BY works with TOP because it restricts the result-set based on the "view" established by the ORDER BY -- however, unless this is a top-level ORDER BY it does not guarantee the order of the final result set (just that the "correct" TOP records are chosen).

That is, 

SELECT FOO FROM (
  SELECT TOP 10 FOO FROM X
  ORDER BY FOO ASC) BAR

Does not have a guaranteed ordering of records in the final result-set; the FOO values can appear in any order.

Happy coding.

Upvotes: 2

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

Try:

SELECT o.OrderID, o.Freight + SUM((d.UnitPrice-d.Discount)*d.Quantity)
FROM tblOrderDetails d
JOIN tblOrders o ON o.OrderId = d.OrderId
GROUP BY o.OrderID, o.Freight
ORDER BY o.OrderID, o.Freight -- Freight avoids a potential re-order

Upvotes: 2

dpmattingly
dpmattingly

Reputation: 1321

You can't have that ORDER BY in the subquery. I'm guessing that you left that in when you were developing that part of the query. Remove that and you'll be fine.

SELECT OrderID, Freight+
    (SELECT SUM((UnitPrice-Discount)*Quantity)
    FROM tblOrderDetails 
    WHERE OrderID= OrderID 
    GROUP BY OrderID)
FROM tblOrders

Upvotes: 1

manji
manji

Reputation: 47978

Remove the ORDER BY OrderID:

SELECT OrderID, Freight+
    (SELECT SUM((UnitPrice-Discount)*Quantity)
    FROM tblOrderDetails 
    WHERE OrderID= OrderID 
    GROUP BY OrderID)
FROM tblOrders

It's illegal in sub queries as the error message states (without TOP or FOR XML) and has no utility for what you need.

Upvotes: 1

Related Questions