PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

3

Answers


  1. 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.
  2. 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.
  3. 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.
Please signup or login to give your own answer.

Back To Top
Search