SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) 
	Sep  7 2018 01:37:51 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE _order
(
  order_id    INT NOT NULL PRIMARY KEY,
  total_price INT NOT NULL
);
INSERT INTO _order VALUES
(1, 1000), (2, 2000), (3, 3000);
3 rows affected

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)
);
INSERT INTO payment VALUES
(1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750);
5 rows affected

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;


order_id
total_price
order_id
sum_pay
owed


1
1000
1
500
500

2
2000
2
2000
0

3
3000
3
2250
750


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;


o_id
owed


1
500

3
750


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;


total_owed_all_orders


1250


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;


o_id
owed
tot_owed


1
500
1250

3
750
1250