SQL Server 2008: Nested Statement

Question

I am trying to write an expression to calculate the total amount of an invoice, each invoice as several products and prices. I got:

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

This works great, now I need to add freight from another table ( tblOrder ), and this is where I got 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 not valid in views, built-in functions, derived tables, subqueries, and a general expression table, unless TOP or FOR XML are also specified.

Any help would be appreciated.

Answer 1

It only means what it says :-)

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

I.e

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

Does not have a guaranteed order of records in the final set of results; FOO values ​​can be displayed in any order.

Happy coding.

Answer 2

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 

Answer 3

Remove ORDER BY OrderID :

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

This is illegal in subqueries, because the status of the error message (without TOP or FOR XML) and has no utility for what you need.

Answer 4

You cannot have ORDER BY in a subquery. I assume that you left this when you designed this part of the request. Remove it and everything will be all right.

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