0

I have two table (Orders, Payments) they are 1 to many relationships (1:N) One order can have many payments

Table 1: Orders

id total_price
1 1000
2 2000
3 3000

Table 2: Payments

order_id(FK of Orders) amount_payed
1 500
2 2000
3 1000
3 500
3 750

some orders are not totally payed, so I wanna get

1- the rest paid of each order .. Like :

order rest payed
1 500
3 750

2- the sum of the rest of all the orders Like: 1250

And Thank you.

2
  • What code do you have so far? Oct 12, 2022 at 10:38
  • I didn't even know where to start with Oct 12, 2022 at 13:22

2 Answers 2

1

To solve your problem, I did the following (all of the code below is available on the fiddle here):

CREATE TABLE _order
(
  order_id    INT NOT NULL PRIMARY KEY,
  total_price INT NOT NULL
);

populate:

INSERT INTO _order VALUES
(1, 1000), (2, 2000), (3, 3000);

I used _order for the table name instead of orders because I don't like plural table names - a table is a set and should have a singular name. It may contain 0, 1 or many records - not necessarily many. I also use snake_case for table names. These are just a personal things - choose a convention and stick to it!

Then:

CREATE TABLE payment
(
  order_id INT NOT NULL,
  amount   INT NOT NULL,
  CONSTRAINT payment_order_id_fk FOREIGN KEY (order_id) REFERENCES _order (order_id)
);

and:

INSERT INTO payment VALUES
(1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750);

and run the following SQL:

SELECT
  o.order_id, o.total_price, t.order_id, t.sum_pay, o.total_price - t.sum_pay AS owed
FROM
  _order o
JOIN
(
  SELECT
    order_id, SUM(amount) AS sum_pay
  FROM
    payment
  GROUP BY order_id
) AS t
ON o.order_id = t.order_id
ORDER BY o.order_id;

Result:

order_id total_price order_id sum_pay   owed
       1        1000        1     500    500
       2        2000        2    2000      0
       3        3000        3    2250    750

I left the extra fields in there so that you can see what's going on. We can clean this up as follows to give your desired result:

SELECT
  o.order_id AS o_id, o.total_price - t.sum_pay AS owed
FROM
  _order o
JOIN
(
  SELECT
    order_id, SUM(amount) AS sum_pay
  FROM
    payment
  GROUP BY order_id
) AS t
ON o.order_id = t.order_id
WHERE o.total_price - t.sum_pay != 0
ORDER BY o.order_id;

Result:

o_id    owed
   1     500
   3     750

and to get the overall sum of what's owed for every order, we do the following:

SELECT
  SUM(owed) AS total_owed_all_orders
FROM
(  
  SELECT
    o.order_id AS o_id, o.total_price - t.sum_pay AS owed
  FROM
    _order o
  JOIN
  (
    SELECT
      order_id, SUM(amount) AS sum_pay
    FROM
      payment
    GROUP BY order_id
  ) AS t
  ON o.order_id = t.order_id
  WHERE o.total_price - t.sum_pay != 0
) AS t2;

Result:

total_owed_all_orders
                 1250

Et voilà - results as requested.

You might also want to try something like the following if you wish to avoid too many subselects - and you get all of your desired data in one go, so to speak.

SELECT
  o.order_id AS o_id, o.total_price - t.sum_pay AS owed, SUM(o.total_price - t.sum_pay) OVER () AS tot_owed
FROM
  _order o
JOIN
(
  SELECT
    order_id, SUM(amount) AS sum_pay
  FROM
    payment
  GROUP BY order_id
) AS t
ON o.order_id = t.order_id
WHERE o.total_price - t.sum_pay != 0
ORDER BY o.order_id;

Result:

o_id    owed    tot_owed
   1     500        1250
   3     750        1250

Window functions are well worth getting to know as they are very powerful and will repay any effort spent learning them many times over! The best presentation I've ever seen about them is this by Bruce Momjian (a PostgreSQL core team member) - it's about PostgreSQL, but the same basics will apply. Other presentations by him available here.

2
  • 1
    LEFT JOIN and ISNULL might be wise, in case there are no payments at all Oct 12, 2022 at 11:42
  • Thank you so much for all the great explaination and the resources I usually use the singular name to name my tables but this time just not to mix it with Order, but I liked your naming_convention And also in my case I had a very big number so I filterd it by the validtion date i added AND O.Validated_at BETWEEN '2022-10-01 06:53:02.000' AND '2022-10-12 06:53:02.000' before the last line, thank you again and I would like to suggest me a great book or more videos to be as great as you're Oct 12, 2022 at 13:16
1

You can also do this using an APPLY subquery.

Note that OUTER APPLY works like a left-join (as opposed to CROSS APPLY) and will give you Orders even if there are no Payments at all for it.

SELECT
  o.id,
  remaining = o.total_price - ISNULL(p.payed, 0)
FROM Orders o
OUTER APPLY (
    SELECT payed = SUM(p.amount_payed)
    FROM Payments p
    WHERE p.order_id = o.id
) p
WHERE o.total_price > ISNULL(p.payed, 0);

db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.