Sql-server – If at least a negative number, sum; otherwise just show

aggregategroup bysql serversql-server-2016window functions

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

Best Answer

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);
Related Question