# 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);
``````