 ## 4 replies

Last post Sep 30, 2019 01:47 PM by limno

• ### Sum query

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

• ### Re: Sum query

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

• ### Re: Sum query

Hi,

Try something like this:

```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
```

Hope this help

• ### 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.

```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```

And here's the result: Best Regard,

Yang Shen

• ### Re: Sum query

```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```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm