SELECT * FROM INVOICE;

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:

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

CodePudding user response：

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

CodePudding user response：

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.