I'm trying to get the total order amounts to appear along with the top 5 customers who spent over $25k.

SELECT TOP 5 o1.CustomerID, o1.OrderDate,
(SELECT SUM(od1.UnitPrice * od1.Quantity *(1-od1.Discount))
FROM [Order Details] od1
WHERE o1.OrderID = od1.OrderID) AS "Totals"
FROM Orders o1
WHERE o1.CustomerID IN
(SELECT c.CustomerID
FROM Customers c
JOIN Orders o
    ON c.CustomerID = o.CustomerID
JOIN [Order Details] od
    ON o.OrderID = od.OrderID
GROUP BY c.CustomerID
HAVING SUM(UnitPrice * (1-Discount) * Quantity) > 25000
)
ORDER BY o1.OrderDate

I keep getting weird numbers that aren't over 25,000. Have I done something wrong? Is there a better way to do this (without going beyond the scope of the book thus far)?

Thanks!

Hi fordpickup,

I have a solution but it is quite a lenghty code involving Views and derived tables. I imagine that there may be a much shorter way out. You can Select as many columns as you want to see at the last batch. Pls send a feed back and let's know how it goes.


USE Northwind
GO

CREATE VIEW LatestOrders
AS

SELECT TOP 5 *
FROM Orders oo

WHERE CustomerID IN
(SELECT c.CustomerID
FROM Customers c
JOIN Orders o
    ON c.CustomerID = o.CustomerID
JOIN [Order Details] od
    ON o.OrderID = od.OrderID
GROUP BY c.CustomerID
HAVING SUM(UnitPrice * (1-Discount) * Quantity) > 25000
)
ORDER BY OrderDate

GO

CREATE VIEW TopOrders
AS

SELECT CustomerID,Total FROM
(SELECT Orders.CustomerID,SUM(([Order Details].Quantity * [Order Details].UnitPrice) *
 (1 - [Order Details].Discount))
                      AS Total
FROM [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
                      Customers ON Orders.CustomerID = Customers.CustomerID
GROUP BY Orders.CustomerID)AS dt1

WHERE Total >25000

GO

SELECT lo.OrderDate,tpo.CustomerID,tpo.Total
FROM TopOrders tpo
JOIN
LatestOrders lo
ON lo.CustomerID = tpo.CustomerID

GO

--Sylva.