• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1222
  • Last Modified:

CONVERT number to decimal with SQL

I have the following SELECT statement (see code) and I am doing a division calculation (sumscore1 / count_deptname).  The math works, but it returns a whole number.  I would like the number to have one decimal place.

How do I CONVERT that calculation within the SELECT statement?
SELECT     DepartmentName, SumScore1, SumScore1 / Count_DeptName AS AverageScore1, SumScore2, SumScore3, Count_DeptName
FROM         vwDepartmentSummary

Open in new window

0
szadroga
Asked:
szadroga
  • 5
  • 4
  • 4
  • +1
3 Solutions
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Use cast like:
declare @int1 as integer
declare @int2 as integer
set @int1 = 5
set @int2 = 3
select cast(@int1 / @int2 as decimal(10,2))
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
In your case:
SELECT     DepartmentName, SumScore1, Cast(SumScore1 / Count_DeptName as decimal(10,1)) AS AverageScore1, SumScore2, SumScore3, Count_DeptName
FROM         vwDepartmentSummary

/Marten
0
 
szadrogaAuthor Commented:
What does the (10,1) represent.  i assume the 1 represents the number of decimal places, but what does the 10 represent in the parameters?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
szadrogaAuthor Commented:
I am still not getting the proper calculation.  The calculation occurring is 14 / 5 and I am getting 2.0, instead of 2.8 ... Is there something i am doing wrong in the calculation, do i need to write a different function?
0
 
ralmadaCommented:
Check this out:
SELECT     DepartmentName, SumScore1, Cast(SumScore1 as decimal(10,1)) / cast(Count_DeptName as decimal(10,1))  AS AverageScore1, SumScore2, SumScore3, Count_DeptName
FROM         vwDepartmentSummary

10 is the precision, that is the maximum total number of decimal digits that can be stored both to the left and to the right of the decimal point. Have a look:
http://msdn.microsoft.com/en-us/library/ms187746.aspx 
0
 
BrandonGalderisiCommented:
The problem is that you need to have one of the fields in the decimal data type before doing the calculation.

You can do that simply by multiplying the first value by 1.0 or casting it as a decimal datatype.
SELECT     DepartmentName, SumScore1, (SumScore1*1.0) / Count_DeptName AS AverageScore1, SumScore2, SumScore3, Count_DeptName
FROM         vwDepartmentSummary

Open in new window

0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
BrandonGalderis solution is sweet, multiply by 1.0 gives you a decimal, though it gives far to many decimals. Combine like:
declare @int1 as integer
declare @int2 as integer
set @int1 = 14
set @int2 = 5
select cast((@int1*1.0 / @int2) as decimal(10,1))
In your case
SELECT     DepartmentName, SumScore1, cast((SumScore1*1.0) / Count_DeptName as decimal(10,1)) AS AverageScore1, SumScore2, SumScore3, Count_DeptName
FROM         vwDepartmentSummary

/Marten
0
 
ralmadaCommented:
I'm sorry, but I'm not happy with the way you split the points.
1) You accepted a solution that is not valid (comment 24375667 is not giving you the result you wanted)
2) I have provided you an alternative and I even answered you what the 10 in "decimal(10,1)" means.
I feel I should have received some points. Asker, can you please review this question?
Thanks
0
 
szadrogaAuthor Commented:
Did I not split the points evenly?  How do I go about re-assigning points?
0
 
ralmadaCommented:
Click on the "request attention" link and ask for the points to be reassigned.
0
 
BrandonGalderisiCommented:
I'd have to agree with ralmada here.  I believe that a 4x split is the fairest.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
+1
(Means agreeing with the speaker above, i e BrandonGalederisi)

/Marten
0
 
szadrogaAuthor Commented:
I am only seeing 3 different responders...am I missing something?
0
 
BrandonGalderisiCommented:
3x split... sorry... here is MY opinion.

http:#a24375866 by ralmada - 250 for the first working solution
http:#a24376023 by brandongalderisi - 125 for my post
http:#a24376411 by martenrune - 125 for fixing my above post to limit decimal points

marten... none of your previous posts prior to the one I reference will perform the math correctly since the result will be an integer, cast to an decimal.
0
 
szadrogaAuthor Commented:
Tried to divide as evenly as possible.  thanks for all the help and quick responses
0
 
BrandonGalderisiCommented:
I think that is the fairest split given timeliness and accuracy.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now