March 6, 2009 at 9:14 am
drop table #temp
select distinct Empid, jobid , salary, age
into #temp
from Revenue.dbo.BudgetRevCA
union
select distinct Empid, jobid, salary, age
from Revenue.dbo.BudgetRevNV
union
select distinct Empid, jobid, salary, age
from Revenue.dbo.BudgetRevVA
union
select distinct Empid, jobid, salary, age
from OBBEAVER3.Revenue.dbo.BudgetRev1
union
select distinct Empid, jobid, salary, age
from OBBEAVER3.Revenue.dbo.BudgetRev2
union
select distinct Empid, jobid, salary, age
from OBBEAVER3.Revenue.dbo.BudgetRev3
union
select distinct Empid, jobid, salary, age
from OBBEAVER3.Revenue.dbo.BudgetRev4
union
select distinct Empid, jobid, salary, age
from OBBEAVER3.Revenue.dbo.BudgetRev5
union
select distinct Empid, jobid, salary, age
from OBBEAVER5.Revenue.dbo.BudgetRev6
union
select distinct Empid, jobid, salary, age
from OBBEAVER5.Revenue.dbo.BudgetRev7
union
select distinct Empid, jobid, salary, age
from OBBEAVER5.Revenue.dbo.BudgetRev8
union
select distinct Empid, jobid, salary, age
from OBBEAVER5.Revenue.dbo.BudgetRev9
union
select distinct Empid, jobid, salary, age
from OBBEAVER5.Revenue.dbo.BudgetRev10
go
select avg(salary) as AVGSAL, avg(age) as AVEAGE
from #temp
Select statement works fine but i get this error for the average.
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
is there a best way i can get this compare to my way.
thanks
March 6, 2009 at 9:33 am
Try casting salary and/or age to datatypes large enough to store the total sum of these columns. You could use bigint, float or the decimal data types. It's probably the salary sum rather than the age sum that is overflowing of course, so you probably won't need to cast the age column.
SELECT AVG(CAST(salary AS decimal(16,2))) AS AVGSAL, AVG(age) AS AVEAGE
FROM #temp
March 6, 2009 at 10:38 am
Or cast at table *creation time*
drop table #temp
select distinct Empid, jobid , CAST(salary AS decimal(16,2)) as salary, age
into #temp
....
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply