[SQL] How can I select rows from a table where the sum of rows in a joined table is greater...

I have a order table and a payment table where multiple payments are joined to an order.

order

id name
1 order 1
2 order 2

payment

id order_id amount
1 1 2000
2 1 3000
3 2 500
4 2 100

I want to SELECT all orders where the SUM of the payments are greater or less than a specific amount. How can I do that?

I think I need to JOIN the tables but I'm not sure how to SUM the amount in the payment table. I tried this:

SELECT * FROM "order"
JOIN payment ON payment.order_id = payment.id
WHERE (SELECT SUM(amount) FROM payment) > 2000


This does not result in any errors but I don't think the results are correct. I am expecting only "order 1" to be returned. Do I need to use GROUP BY or HAVING somehow?

Continue reading...