Solved

CONVERT number to decimal with SQL

Posted on 2009-05-13
17
1,110 Views
Last Modified: 2012-06-27
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
Comment
Question by:szadroga
  • 5
  • 4
  • 4
  • +1
17 Comments
 
LVL 20

Expert Comment

by:Marten Rune
Comment Utility
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

by:Marten Rune
Comment Utility
In your case:
SELECT     DepartmentName, SumScore1, Cast(SumScore1 / Count_DeptName as decimal(10,1)) AS AverageScore1, SumScore2, SumScore3, Count_DeptName
FROM         vwDepartmentSummary

/Marten
0
 

Author Comment

by:szadroga
Comment Utility
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

by:szadroga
Comment Utility
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

by:
ralmada earned 250 total points
Comment Utility
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

by:BrandonGalderisi
BrandonGalderisi earned 125 total points
Comment Utility
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
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 125 total points
Comment Utility
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
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:szadroga
Comment Utility
Did I not split the points evenly?  How do I go about re-assigning points?
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
Click on the "request attention" link and ask for the points to be reassigned.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
I'd have to agree with ralmada here.  I believe that a 4x split is the fairest.
0
 
LVL 20

Expert Comment

by:Marten Rune
Comment Utility
+1
(Means agreeing with the speaker above, i e BrandonGalederisi)

/Marten
0
 

Author Comment

by:szadroga
Comment Utility
I am only seeing 3 different responders...am I missing something?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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

by:szadroga
Comment Utility
Tried to divide as evenly as possible.  thanks for all the help and quick responses
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
I think that is the fairest split given timeliness and accuracy.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now