Como substruir algumas linhas de tabela de outras linhas de tabela relacionadas e obter a SOMA do resultado total

Para resolver seu problema, fiz o seguinte (todo o código abaixo está disponível no violino aqui ):

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

preencher:

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

Eu usei _orderpara o nome da tabela em vez de ordersporque não gosto de nomes plurais de tabela - uma tabela é um conjunto e deve ter um nome singular. Pode conter 0, 1 ou muitos registros - não necessariamente muitos. Eu também uso snake_case para nomes de tabelas. Estas são apenas coisas pessoais - escolha uma convenção e cumpra-a!

Então:

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)
);

e:

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

e execute o seguinte 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;

Resultado:

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

Deixei os campos extras lá para que você possa ver o que está acontecendo. Podemos limpar isso da seguinte maneira para obter o resultado desejado:

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;

Resultado:

o_id    owed
   1     500
   3     750

e para obter a soma total do que é devido para cada pedido, fazemos o seguinte:

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;

Resultado:

total_owed_all_orders
                 1250

Et voilà - resultados conforme solicitado.

Você também pode tentar algo como o seguinte se desejar evitar muitas subseleções - e obter todos os dados desejados de uma só vez, por assim dizer.

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;

Resultado:

o_id    owed    tot_owed
   1     500        1250
   3     750        1250

Vale a pena conhecer as funções de janela , pois são muito poderosas e compensarão qualquer esforço gasto em aprendê-las muitas vezes! A melhor apresentação que eu já vi sobre eles é esta de Bruce Momjian (um membro da equipe principal do PostgreSQL) - é sobre o PostgreSQL, mas os mesmos fundamentos serão aplicados. Outras apresentações dele disponíveis aqui .

Você também pode fazer isso usando uma APPLYsubconsulta.

Observe que OUTER APPLYfunciona como uma junção à esquerda (em oposição a CROSS APPLY) e fornecerá a você Ordersmesmo que não haja Paymentsnada para isso.

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<>violino