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.

---

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

---

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.

---

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.

---

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