Sum of a decimal field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sum of a decimal field

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2011-12-15 : 17:42:38

Hi All,

I am trying to do a sum of a tax filed but not getting the correct result .Here's my schema:

create table test2
(
zid int not null,
zip varchar (10) not null,
city varchar (100) null,
state varchar (5) null
)

insert into test2 (zid,zip,city,state)
values (1,'00001','New York','NY')
insert into test2 (zid,zip,city,state)
values (2,'00001','Long Island','NY')

create table test
(
zip varchar (10) not null,
tax decimal(10, 2) NULL
)

insert into test
values ('00001',8.73)
insert into test
values ('00001',9.00)
insert into test
values ('00001',5.71)
insert into test
values ('00001',18.60)

select * from test2
-------------------------
zid zip city state
1 00001 New York NY
2 00001 Long Island NY

select * from test
----------------------
zip tax
00001 8.73
00001 9.00
00001 5.71
00001 18.60

Now if I run this query

select t2.zip,t2.state,sum (t.tax) as total
from test2 t2
inner join test t on t2.zip = t.zip
where t.zip = '00001'
group by t2.zip,t2.state

I get
-----------------------------

zip state total
00001 NY 84.08

The total 84.08 is incorrect The correct total should be 8.73 + 9.00 + 5.71 + 18.60 = 42.04

I know the reason why i am getting 84.08 total (42.04 x 2) . It is because I have 2 raws in table test2 for zip 00001 with 2 diffrenet city. But that it what i want. I want total tax by zip.
Please help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-15 : 19:38:03

[code]
select distinct t.zip, t2.state, t.tax
from
(
select zip, tax = sum(tax)
from test
group by zip
) t
inner join test2 t2 on t.zip = t2.zip
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ssunny
Posting Yak Master

133 Posts

Posted - 2011-12-16 : 11:24:15

Thank you so much khtan. Works like a charm. Exactly what I need. Thanks.

ssunny
Posting Yak Master

133 Posts

Posted - 2011-12-16 : 15:49:00

So the requirement has changed. So now my tables looks like this:
create table test
(
zip varchar (12),
region varchar (25),
state varchar (5)
)
insert into test
values ('100','sussex','nj')
insert into test
values ('200','sussex','nj')
insert into test
values ('100','sussex','nj')
insert into test
values ('100','sussex','nj')
insert into test
values ('300','sussex','nj')

create table test1
(
zip varchar (12),
amount decimal (10,2)
)
insert into test1
values ('100',3.25)
insert into test1
values ('200',5.60)
insert into test1
values ('300',11.25)

select * from test
---------------------
zip region state
100 sussex nj
200 sussex nj
100 sussex nj
100 sussex nj
300 sussex nj

select * from test1
-------------------------
zip amount
100 3.25
200 5.60
300 11.25

so when I run following

select distinct t.region,t.state,t1.amount
from
(
select zip,amount = sum (amount)
from test1
group by zip
) t1
inner join test t on t1.zip = t.zip

I get

region state amount
sussex nj 3.25
sussex nj 5.60
sussex nj 11.25

What I want is one row per region and state. basically I want amount by region and state.

So the OUTPUT I want shpuld be:

region state amount
sussex nj 20.01

Thanks in advace.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-16 : 21:41:30

why do you have duplicate records in test table ?


select * from test
---------------------
zip region state
100 sussex nj
200 sussex nj
100 sussex nj
100 sussex nj
300 sussex nj

KH
[spoiler]Time is always against us[/spoiler]

ssunny
Posting Yak Master

133 Posts

Posted - 2011-12-20 : 16:26:28

Sorry for the late reply khtan. I got this working. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources