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

MichaelLynch
October 4, 2024
155 views
3 votes
3 Answers

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?

Answers

- Bohemian
- October 4, 2024 at 12:22 am
- 0 votes
0
Use a subquery to find the id’s of orders with more than 2000 payment amounts:
```
SELECT * FROM "order"
JOIN payment ON payment.order_id = payment.id
WHERE "order".id in (
  SELECT order_id
  FROM payment
  GROUP BY 1
  HAVING SUM(amount) > 2000
)
```
Handy note: You don’t need to select an aggregated value to use it in a HAVING clause
Login or Signup to reply.

- tinazmu
- October 4, 2024 at 1:56 am
- 0 votes
0
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.order_id=p1.order_id ) > 2000
```
There are other ways of doing the same.
Login or Signup to reply.

- AbuBenReaz
- October 4, 2024 at 2:42 am
- 0 votes
0
You need to use GROUP BY and HAVING clause

Just use the basic structure of GROUP BY and HAVING clause
```
SELECT a.ID, a.Name, sum(b.Amount) as TotAmt FROM public."order" a 
JOIN public.payment b ON a.ID=b.Order_ID
GROUP BY a.ID, a.Name HAVING sum(b.Amount) > 2000;
```
If you want to drop the column (sum(b.Amount) as TotAmt) from the result set just remove this column like it
```
SELECT a.ID, a.Name FROM public."order" a 
JOIN public.payment b ON a.ID=b.Order_ID
GROUP BY a.ID, a.Name HAVING sum(b.Amount) > 2000;
```
Login or Signup to reply.