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

user_1681247 0
Viewed 94
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
sql-server sql
2 Answers

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

enter image description here

user_15700000

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.

user_104988280
Related