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), (4, 4000);
4 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)
);
CREATE INDEX pt_order_id_ix ON payment (order_id);
INSERT INTO payment VALUES
(1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750);  -- note - no payment for order_id = 4
5 rows affected

SELECT
  o.order_id,
  o.total_price - COALESCE(p.paid, 0) AS remaining
FROM _order o
CROSS APPLY (
    SELECT SUM(p.amount) AS paid
    FROM payment p
    WHERE p.order_id = o.order_id
) AS p
WHERE o.total_price > ISNULL(p.paid, 0);


order_id
remaining


1
500

3
750

4
4000