with CTE_Sum as
(
select
*,
sum(Amount) over(partition by R_no) as SumAmount,
case when Amount < 0 then 'N' else 'P' end as PosNeg
from MyTable
)
select
R_no,
sum(Amount) as Amount
from CTE_Sum
where
SumAmount <> 0
group by grouping sets
( (PosNeg), (R_no, Amount) )
order by
grouping_id(R_no, Amount),
R_no

For the first query, you will only need a
group by to filter out the data whose sum of amount is 0.

For the second, which is even easier, you just need the
sum() method to select the data.

Please refer to below demo:

create table Maths(R_number int,Amount int)
insert into Maths values(451,591)
insert into Maths values(451,-591)
insert into Maths values(787,2325)
insert into Maths values(787,-63333)
insert into Maths values(474,-774)
insert into Maths values(474,774)
insert into Maths values(889,-9666)
insert into Maths values(889,7541)
insert into Maths values(644,-8844)
insert into Maths values(644,2545)
--select * from Maths
select a.R_no into #AAA from (select R_number as R_no,SUM(Amount) as Amount from Maths group by R_number)a where a.Amount=0
select R_number as R_no,Amount from Maths where R_number not in (select R_no from #AAA)select sum(Amount) as amount from Maths where R_number not in (select R_no from #AAA) and Amount<0
union all
select sum(Amount) as amount from Maths where R_number not in (select R_no from #AAA) and Amount>0
drop table Maths
drop table #AAA

create table test(R_number int,Amount int)
insert into test values
(474,-774)
,(474,774)
,(889,-9666)
,(889,7541)
,(644,-8844)
,(644,2545)
;with mycte as (
select *
, sum( Amount) Over(Partition by R_number ) mysum
from test
)
Select /*sign(amount) mysign ,*/ SUM(Amount) Amount
from mycte
WHERE mysum<>0
Group by sign(amount)
drop table test

Member

12 Points

162 Posts

## Sum query

Sep 28, 2019 12:52 PM|thepast|LINK

Hi

I have 2 coulumns

R_no Amount

451 591

451 -591

787 2325

787 -63333

what I want select query show only values which sum of them no equal to (0) in my example should only shows

R_no Amount

787 2325

787 -63333

How can I make that query

Member

12 Points

162 Posts

## Re: Sum query

Sep 28, 2019 01:07 PM|thepast|LINK

and also if there positive numbers will do summation and negative numbers

for example

R_number Amount

474 -774

474 774

889 -9666

889 7541

644 -8844

644 2545

the result must shows ( first check in same group if summation = 0 eliminate them then summation of positive numbers alone and negative numbers alone)

amount

-18,510

10,086

Participant

1150 Points

303 Posts

## Re: Sum query

Sep 28, 2019 02:36 PM|imapsp|LINK

Hi,

Try something like this:

Hope this help

Contributor

3140 Points

983 Posts

## Re: Sum query

Sep 30, 2019 03:28 AM|Yang Shen|LINK

Hi thepast,

For the first query, you will only need a group by to filter out the data whose sum of amount is 0.

For the second, which is even easier, you just need the sum() method to select the data.

Please refer to below demo:

And here's the result:

Best Regard,

Yang Shen

All-Star

123252 Points

10024 Posts

Moderator

## Re: Sum query

Sep 30, 2019 01:47 PM|limno|LINK

Format your SQL query with instant sql formatter:

http://www.dpriver.com/pp/sqlformat.htm