[SOLVED] How can I select rows from a table where the sum of rows in a joined table is greater than a number?

How can I select rows from a table where the sum of rows in a joined table is greater than a number?

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?

Solution

You were on the right track, but you need to 'correlate' the subquery to the row in the main query:

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

There are other ways of doing the same.