Summing a column

Transact-SQL
1

Hi i have this data:
column1 column2 column3 (numeric)
00000094588050 002 2.0000
00000094588050 002 1.0000
00000094588053 001 1.0000
00000094588053 002 -1.0000
00000094588055 001 1.0000
00000094588057 001 1.0000
00000094588155 002 1.0000
00000094588155 002 -1.0000
00000094588155 002 1.0000
00000094588155 002 2.0000

how would i query the data so i get this result:
00000094588050 002 3.0000
00000094588053 001 0 (omit this because it is zero)
00000094588055 001 1.0000
00000094588057 001 1.0000
00000094588155 002 3.0000

I have been playing around for a bit to no avail
Thanks!

2

This is a simple GROUP BY query with a HAVING...please show us what you have tried so far.

3

Declare @t table(column1 varchar(20),column2 varchar(20),Column3 decimal(10,4))

Insert into @t values ('00000094588050','002', 2.0000)
Insert into @t values ('00000094588050','002', 1.0000)
Insert into @t values ('00000094588053','001', 1.0000)
Insert into @t values ('00000094588053','001', -1.0000)
Insert into @t values ('00000094588055','001', 1.0000)
Insert into @t values ('00000094588057','001', 1.0000)
Insert into @t values ('00000094588155','002', 1.0000)
Insert into @t values ('00000094588155','002', -1.0000)
Insert into @t values ('00000094588155','002', 1.0000)
Insert into @t values ('00000094588155','002',2.0000)

Select column1
,Column2
,case when Cast(Sum(column3) as int)=0 then ''
else
Cast (Sum(column3) as varchar(20))
end
from @t
group by Column1,Column2

4

To exclude the row where the values are 0 - instead of a blank add a having clause:

from @t
group by Column1, Column2
having sum(column3) <> 0 --edited to exclude rows with zero

5

You can use windows functions also. Try this

Declare @t table(column1 varchar(20),column2 varchar(20),Column3 decimal(10,4))
Insert into @t values ('00000094588050','002', 2.0000)
Insert into @t values ('00000094588050','002', 1.0000)
Insert into @t values ('00000094588053','001', 1.0000) 
Insert into @t values ('00000094588053','001', -1.0000)
Insert into @t values ('00000094588055','001', 1.0000) 
Insert into @t values ('00000094588057','001', 1.0000)
Insert into @t values ('00000094588155','002', 1.0000)
Insert into @t values ('00000094588155','002', -1.0000) 
Insert into @t values ('00000094588155','002', 1.0000) 
Insert into @t values ('00000094588155','002',2.0000)


select distinct column1, column2, sum(column3)over (partition by column1 order by column1) as result from @t