By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH sales AS (
SELECT 1 AS PersonID, 10 AS amount UNION ALL
SELECT 1, 10 UNION ALL
SELECT 2, 30 UNION ALL
SELECT 3, 40
)
SELECT PersonID,
MAX(SUM(amount)) OVER () - SUM(amount) AS amount
FROM sales
GROUP BY PersonID;
| PersonID | amount |
|---|---|
| 1 | 20 |
| 2 | 10 |
| 3 | 0 |