How to sum up and other calculations by the other columns in the same table?

Question

SELECT * FROM INVOICE;

id	amount
1	20
2	20
3	20
4	30
5	100
6	20
7	30
8	100

I would like to create three more columns which will be calculated by the amount column.

openamt  = Sum of amount
debamt   = Sum of amount where amount < 50
credamt  = Sum of amount where amount > 50
closeamt = openamt - debamt + credamt

Expected output:

id	amount	openamt	debamt	credamt	closeamt
1	20	340	140	200	400
2	20	340	140	200	400
3	20	340	140	200	400
4	30	340	140	200	400
5	100	340	140	200	400
6	20	340	140	200	400
7	30	340	140	200	400
8	100	340	140	200	400

What I tried, I got it working for openamt but not for the rest. Can somebody point out me to the right direction, please?

SELECT 
    ID, 
    AMOUNT, 
    SUM(AMOUNT) OVER () AS OPENAMT,
    (SELECT SUM(AMOUNT) FROM INVOICE WHERE AMOUNT < 0) AS DEBTAMT,  -- This is how I want
    (SELECT SUM(AMOUNT) FROM INVOICE WHERE AMOUNT > 0) AS CREDAMT,   -- This is how i want
    (OPENAMT - DEBTAMT + CREDAMT) AS CLOSEAMT
FROM 
    INVOICE

Answer 1

If the definition of credamt includes the = sign also like this:

credamt = Sum of amount where amount >= 50

then closeamt is just twice closeamt because obviously:

openamt = debamt + credamt

so:

closeamt = openamt - debamt + credamt
         = (debamt + credamt) - debamt + credamt
         = 2 * credamt

Use SUM() window function:

SELECT *, 
       SUM(amount) OVER() openamt,
       SUM(CASE WHEN amount < 50 THEN AMOUNT ELSE 0 END) OVER() debamt,
       SUM(CASE WHEN amount >= 50 THEN AMOUNT ELSE 0 END) OVER() credamt,
       2 * SUM(CASE WHEN amount >= 50 THEN AMOUNT ELSE 0 END) OVER() closeamt
FROM tablename;

See the demo.

Answer 2

You were in the right area. You can use a conditional aggregation within the window function sum() over()

Example

Declare @YourTable Table ([id] int,[amount] int)  Insert Into @YourTable Values 
 (1,20)
,(2,20)
,(3,20)
,(4,30)
,(5,100)
,(6,20)
,(7,30)
,(8,100)
 
Select * 
      ,openamt  = sum(amount) over()
      ,debamt   = sum( case when amount<50 then amount end) over()
      ,credamt  = sum( case when amount>=50 then amount end) over()
      ,closeamt = sum(amount) over()
                 -sum( case when amount<50 then amount end) over()
                 +sum( case when amount>=50 then amount end) over()
 From @YourTable

Results