Sum over Partition By Only when Value is Greater than 0

Question

I only want to sum the applied amount when a ledger amount in another table is positive

Example

Table A

Statement #            ID
500                     1
500                     2
500                     3
500                     4

Table B

Ledger_Amount  Type        ID      
-389.41        Credit      1          
-1218.9        Credit      2          
-243.63        Credit      3          
3485.19        Invoice     4

Table C

Applied_Amount           ID 
389.41                    1
1218.9                    2
243.63                    3
1633.25                   4

The current code is

(sum(applied_amount) over (partition by statement_number),0)

It is coming up with a total of $3485.19 because it is summing by statement number only, and all IDs have the same statement number, the value I want it to come up with is $1633.25 because it should not sum anything where the ledger_amount in table B is less than 0, so ID 1,2,3 should not be summed only valid value is ID 4

Add create and insert statements to your question or use a site like dbfiddle.uk — Lennart - Slava Ukraini, Dec 09 '22 at 19:15
`sum(case when Ledger_Amount <=0 then 0 else ledger_Amount END ) over(partition by statement_number)` assuming all 3 tables are joined in from of your query. — xQbert, Dec 09 '22 at 19:18
Possible slight correction to @xQbert's comment above: `sum(case when Ledger_Amount <=0 then 0 else Applied_Amount END ) over(partition by statement_number)`, which conditionally sums `Applied_Amount` instead of `Ledger_Amount`. — T N, Dec 11 '22 at 04:06
@TN I tried this but got the same number, it still summed the entire thing — user20736472, Dec 13 '22 at 05:25
@user20736472 - Please update your post with your latest query, test data, plus actual and expected results. Even better would be a minimally reproducable example coded in a [db<>fiddle](https://dbfiddle.uk/-SXv3GHO) with the resulting URL included in your update above. — T N, Dec 13 '22 at 20:00
I've got more, but I'll await your updated query first. (/EnaYwHAQ/) — T N, Dec 13 '22 at 20:33

score 0 · Answer 1 · answered Dec 09 '22 at 19:20

There is one approach:

Assuming ID is a unique column, first we should get the IDs we'd work on based on the statementnumber and save them in a temp table:

select Id
into #Ids
from tableA
where StatementNumber=@yourStatementNumber

Then, eliminate the IDs where they have a negative number in table B

Select Id 
into #IdsWithPositiveLedger
From #Ids
Where Id in (
   Select ID
   From tableB
   Where Ledger_Amount>0 
)

Finally, use the ids left to get your sum:

Select sum(applied_amount)
from tableC
where Id in (select Id from #IdsWithPositiveLedger)

Sum over Partition By Only when Value is Greater than 0

1 Answers1