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

Questions

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

November 17, 2021

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

>Solution :

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