Many times we have requirements of some calculations amongst different fields in Tables. One of the software developers here was trying to calculate some fields having integer values and divide it which gave incorrect results in integer where accurate results including decimals was expected.
Something as follows,
Example,
USE [AdventureWorks]
GO
CREATE TABLE [dbo].ConvertExample(
[ID] [int] NULL,
[Field1] [int] NULL,
[Field2] [int] NULL,
[Field3] [int] NULL,
[Field4] [int] NULL
)
GO
INSERT INTO [dbo].ConvertExample
VALUES (1,30,40,60,80)
GO
INSERT INTO [dbo].ConvertExample
VALUES (2,20,10,50,80)
GO
INSERT INTO [dbo].ConvertExample
VALUES (3,15,140,90,60)
GO
INSERT INTO [dbo].ConvertExample
VALUES (1,60,0,5,2)
GO
SELECT *
FROM [dbo].ConvertExample
GO
SELECT (SUM(Field1)+SUM(Field2)+SUM(Field3))/SUM(Field4)
AS AnswerInt
FROM [dbo].ConvertExample
GROUP BY ID
GO
However, We need to CAST or CONVERT the numerator to solve this or we can say that to get the results we wanted.
Example using CAST
SELECT CAST((SUM(Field1)+SUM(Field2)+SUM(Field3))AS FLOAT)/SUM(Field4) AS AnswerFloat1
FROM [dbo].ConvertExample
GROUP BY ID
GO
Example Using CONVERT:
SELECT CONVERT(FLOAT,(SUM(Field1)+SUM(Field2)+SUM(Field3)))/SUM(Field4) AS AnswerFloat2
FROM [dbo].ConvertExample
GROUP BY ID
GO
Conclusion
When we expect the results in floating or decimals then we need to convert or cast the numerator part to get the accurate results with FLOAT or DECIMAL Whichever needed.
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
Nice post and something that is very much useful in the day-to-day programming life.
Just wanted to add to what is already explained, that, SQL Server automatically does a cast to the data type having the highest precedence. So the result of INT and INT will be INT, but INT and FLOAT will be FLOAT because FLOAT has a higher precedence. If you want a different data type, you need to do an EXPLICIT cast.
For example:
SELECT 100/3 AS Result
/*
Result
———–
33
*/
SELECT 100/3.0 AS Result
/*
Result
—————————————
33.333333
*/
The precedence of each data type is documented here: http://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx
Thank you Jacob,
I like your additional explanation. I just realize after reading your explanation that I should include your explanation in the post itself so it is visible to everybody right away.
Also this has brought another idea in my mind that when creating calculated field the same scenario happens. E.g. When we create calculated field from additions of INT it will give us final calculated field as INT. If we want that in another datatype we should have either follow your suggestion or CAST them.
Regards,
Pinal
Sometimes I get lazy and just do
field = int column A / int column B * 1.0
Then I *think* I get float back
[...] 27, 2008 by pinaldave Yesterday I wrote post about SQL SERVER - Get Answer in Float When Dividing of Two Integer. I received excellent comment from SQL Server MVP Jacob Sebastian. Jacob has clearified the concept [...]
Thanks guys, you make a nice duet together :)
Very interesting about this precedence.
[...] SQL SERVER - Get Answer in Float When Dividing of Two Integer SQL SERVER - Puzzle - Computed Columns Datatype Explanation SQL SERVER - Puzzle - Solution - Computed Columns Datatype Explanation [...]