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