How to substruct some table rows from another related table rows and get the SUM of the totale result

Question

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.

Answer 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.

Answer 2

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