Mixing
If at least a negative number, sum; otherwise just show
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
3
down vote
favorite
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
sql-server sql-server-2016 group-by aggregate window-functions
asked 5 hours ago
Kevin
1264
add a comment |Â
up vote
3
down vote
favorite
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
sql-server sql-server-2016 group-by aggregate window-functions
asked 5 hours ago
Kevin
1264
Can you create an example on db fiddle? I'm an Oracle guy but know how to simplify it withanalytics.
– Michael Kutz
4 hours ago
add a comment |Â
up vote
3
down vote
favorite
up vote
3
down vote
favorite
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
sql-server sql-server-2016 group-by aggregate window-functions
asked 5 hours ago
Kevin
1264
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
sql-server sql-server-2016 group-by aggregate window-functions
sql-server sql-server-2016 group-by aggregate window-functions
asked 5 hours ago
Kevin
1264
asked 5 hours ago
Kevin
1264
edited 3 hours ago
asked 5 hours ago
Kevin
1264
asked 5 hours ago
Kevin
1264
asked 5 hours ago
Kevin
1264
1264
Can you create an example on db fiddle? I'm an Oracle guy but know how to simplify it withanalytics.
– Michael Kutz
4 hours ago
add a comment |Â
Can you create an example on db fiddle? I'm an Oracle guy but know how to simplify it withanalytics.
– Michael Kutz
4 hours ago
Can you create an example on db fiddle? I'm an Oracle guy but know how to simplify it with
analytics.– Michael Kutz
4 hours ago
Can you create an example on db fiddle? I'm an Oracle guy but know how to simplify it with
analytics.– Michael Kutz
4 hours ago
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
2
down vote
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);
answered 3 hours ago
Joe Obbish
19.2k32579
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
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);
answered 3 hours ago
Joe Obbish
19.2k32579
add a comment |Â
up vote
2
down vote
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);
answered 3 hours ago
Joe Obbish
19.2k32579
add a comment |Â
up vote
2
down vote
up vote
2
down vote
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);
answered 3 hours ago
Joe Obbish
19.2k32579
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);
answered 3 hours ago
Joe Obbish
19.2k32579
answered 3 hours ago
Joe Obbish
19.2k32579
answered 3 hours ago
Joe Obbish
19.2k32579
answered 3 hours ago
Joe Obbish
19.2k32579
19.2k32579
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f220618%2fif-at-least-a-negative-number-sum-otherwise-just-show%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Can you create an example on db fiddle? I'm an Oracle guy but know how to simplify it with
analytics.– Michael Kutz
4 hours ago