I have the following database structure :
[Order]
OrderId
Total
[Payment]
OrderId
Amount
Every Order can have X payment rows. I want to get only the list of orders where the sum of all the payments are < than the order Total.
I have the following SQL but I will return all the orders paid and unpaid.
SELECT o.OrderId, o.UserId, o.Total, o.DateCreated, COALESCE(SUM(p.Amount),0) AS Paid
FROM [Order] o
LEFT JOIN Payment p ON p.OrderId = o.OrderId
GROUP BY o.OrderId, o.Total, o.UserId, o.DateCreated
I have tried to add Where (Paid < o.Total) but it does not work, any idea?
BTM I'm using SQL CE 3.5
