# If at least a negative number, sum; otherwise just show

I have a need to sum values together only if there is a negative value present in a partition. If there are no negative values in a partition, it should just output the row.

Here's what I have right now. Initial data is provided as a CTE.

DML

;with ledger as (
select accountId, type, amount
from (
values
(1, 'R', -10)
,(1, 'V', 10)
,(1, 'R', 30)
,(2, 'R', 20)
,(2, 'R', -5)
,(2, 'V', 5)
,(3, 'R', 20)
,(3, 'R', 30)
)   x (accountId, type, amount)
)
,b as ( --identifies accountid, type with negatives
select
accountid
,type
from ledger
group by accountid, type
having min(amount) < 0
)
,onlyPositives as (
select
l.accountid
,l.type
,l.amount
from ledger l
left join b on b.accountid = l.accountid
and b.type = l.type
where b.accountid is null
)
,aggregatedNegatives as (
select
l.accountid
,l.type
,amount = sum(l.amount)
from ledger l
inner join b on b.accountid = l.accountid
and b.type = l.type
group by l.accountid, l.type
)
select  accountid, type, amount
from    onlyPositives

union all

select  accountid, type, amount
from    aggregatedNegatives

I'm expecting an output like this, and the query above outputs correctly.

1, R, 20  (summed because -10+30=20)
1, V, 10  (left alone)
2, R, 15  (summed because 20-5=15)
2, V, 5   (left alone)
3, R, 20  (left alone)
3, R, 30  (left alone)

The query so far looks beastly and feels unnecessarily complex. Is there a simpler query I could write that I have overlooked?

This is a slight variation on the issue: If positive, sum all items. If negative, return each one

rextester -> https://rextester.com/EZRT33825

• Can you create an example on db fiddle? I'm an Oracle guy but know how to simplify it with analytics. – Michael Kutz Oct 19 '18 at 23:25

You can accomplish what you're after using window functions, but I can't make any promises about performance if you're running this query over a lot of rows. The idea is to calculate the sum, minimum, and an unordered row number for every partition. Keep all rows with a minimum > 0 but only keep the first row of a partition if the minimum < 0.

-- put data into temp table for illustration purposes
select accountId, type, amount into #t220618
from (
values
(1, 'R', -10)
,(1, 'R', 30)
,(1, 'V', 10)
,(2, 'R', 20)
,(2, 'R', -5)
,(2, 'V', 5)
,(3, 'R', 20)
,(3, 'R', 30)
)   x (accountId, type, amount);

SELECT
accountId
, type
, CASE WHEN part_min < 0 THEN part_sum else amount END amount
FROM (
SELECT
accountId
, type
, amount
, SUM(amount) OVER (PARTITION BY accountId, type) part_sum
, MIN(amount) OVER (PARTITION BY accountId, type) part_min
, ROW_NUMBER() OVER (PARTITION BY accountId, type ORDER BY (SELECT NULL)) part_rn
FROM #t220618
) q
WHERE q.part_min > 0 OR (part_min < 0 AND part_rn = 1);