Solved

# CONVERT number to decimal with SQL

Posted on 2009-05-13
Medium Priority
1,153 Views
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
``````
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4
• 4
• +1

LVL 20

Expert Comment

ID: 24375651
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

LVL 20

Expert Comment

ID: 24375667
SELECT     DepartmentName, SumScore1, Cast(SumScore1 / Count_DeptName as decimal(10,1)) AS AverageScore1, SumScore2, SumScore3, Count_DeptName
FROM         vwDepartmentSummary

/Marten
0

Author Comment

ID: 24375713
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

Author Comment

ID: 24375823
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

LVL 41

Accepted Solution

ID: 24375866
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

LVL 39

Assisted Solution

BrandonGalderisi earned 500 total points
ID: 24376023
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
``````
0

LVL 20

Assisted Solution

Marten Rune earned 500 total points
ID: 24376411
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))
SELECT     DepartmentName, SumScore1, cast((SumScore1*1.0) / Count_DeptName as decimal(10,1)) AS AverageScore1, SumScore2, SumScore3, Count_DeptName
FROM         vwDepartmentSummary

/Marten
0

LVL 41

Expert Comment

ID: 24381731
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.
Thanks
0

Author Comment

ID: 24384349
Did I not split the points evenly?  How do I go about re-assigning points?
0

LVL 41

Expert Comment

ID: 24384717
Click on the "request attention" link and ask for the points to be reassigned.
0

LVL 39

Expert Comment

ID: 24387130
I'd have to agree with ralmada here.  I believe that a 4x split is the fairest.
0

LVL 20

Expert Comment

ID: 24392584
+1
(Means agreeing with the speaker above, i e BrandonGalederisi)

/Marten
0

Author Comment

ID: 24396916
I am only seeing 3 different responders...am I missing something?
0

LVL 39

Expert Comment

ID: 24397471
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

Author Closing Comment

ID: 31581009
Tried to divide as evenly as possible.  thanks for all the help and quick responses
0

LVL 39

Expert Comment

ID: 24397679
I think that is the fairest split given timeliness and accuracy.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats. Â  Recently, I was involved in a diâ€¦
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonstâ€¦
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: â€¦
###### Suggested Courses
Course of the Month11 days, left to enroll