SUM and ROUND functions

Question

Post reply

SUM and ROUND functions

normanshongo2003

Right there with Babe

Points: 743
More actions
April 14, 2011 at 5:19 am

#238598

Hi FOLKS IM HAVING BOTHER WITH THIS LINE OF CODE..I NEED IT SET TO 0 DECIMAL PLACES?..
select ROUND SUM( annual_salary as decimal ((8,0) as'Total'
from dbo.Employees
where Department_No='80'
i can get it with 2 decimal places but not 0?.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

normanshongo2003 Right there with Babe Points: 743 More actions · Answer 1

This code gets me 2 decimal places ..

select SUM(annual_salary) as 'Total'

from dbo.Employees

where Department_No='80'

this code gets me 0 decimal places with monthly salary

select department_no,cast (Annual_Salary/12 as decimal (8,0))as 'Monthly Salary £ '

from dbo.Employees

where Department_No='80'

having bother with the yearly salary for department 80?..

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

round() and cast() are different functions - check BOL.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

normanshongo2003 (4/14/2011)
This code gets me 2 decimal places ..
select SUM(annual_salary) as 'Total'
from dbo.Employees
where Department_No='80'

The datatype which your table might be having as prec 8,2 so it is returning as 0.00 with 2 decimals

this code gets me 0 decimal places with monthly salary
select department_no,cast (Annual_Salary/12 as decimal (8,0))as 'Monthly Salary £ '
from dbo.Employees
where Department_No='80'

Here you have set decimal as 8,0 so here your are getting as 0.

What is the wrong with the above?

All the things are working fine as per your query correctly.

Thanks
Parthi

normanshongo2003 Right there with Babe Points: 743 More actions · Answer 4

select sum(annual_salary) as 'total'

from dbo.Employees

where Department_No='80'

this displays the total annual salary for department but to 2 decimal places...i just need 0 decimal places

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Since the cast gets you waht you want, apply the cast to the results of the sum.

select cast (sum(annual_salary) as decimal (8,0)) as Total

from dbo.Employees

where Department_No='80'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

normanshongo2003 Right there with Babe Points: 743 More actions · Answer 6

thx so much that worked gila...it ows doing my head in there for ages...lol...thx again pal...:-)