Cast Numeric data type gain during Sum
in [Programming SQL]
|
Prev: Temp table and table variable
Next: Nested Set Model - finding the level 1 parent node of a child node
From: Baiju K U on 26 Jul 2010 04:45 Hello I have a table which has a column called 'Area' which is defined as Numeric data type. I have some calculation on this area field and I made a view which has three fields related to Area. Let the fields be Area1, Area2 , Area3. This area fields are mapped across various Divisions. So Division is also a field in my view When I do a query on these fields like this Select Division, Sum((Area1/Area2)*Area3) as ProratedArea From table Group by Division The Prorated Area looks different from what I have in the next Query Select Division, Sum((Convert(Numeric(20,2),Area1)/Area2)*Area3) as ProratedArea From table Group by Division What is the reason for this difference? The field Area is already numeric and why should I cast the related fields again as numeric? Thanks Baiju
From: Gert-Jan Strik on 26 Jul 2010 12:45 Baiju, Most likely at least one of the columns Area1, Area2, Area3 is not a numeric. In fact, probably all three are int or bigint or something like that. Run this query: SELECT TOP 1 Area1, Area2, Area3 INTO test GO sp_help "test" GO DROP TABLE test The result will show the data type definition of Area1, Area2 and Area3. If "Type" is not "numeric", then obviously they are not numerics. If they are numerics, then check their precision. If the precision is higher than 20, then that could explain the difference as well. -- Gert-Jan Baiju K U wrote: > > Hello > > I have a table which has a column called 'Area' which is defined as Numeric > data type. > I have some calculation on this area field and I made a view which has three > fields related to Area. Let the fields be > Area1, Area2 , Area3. This area fields are mapped across various Divisions. > So Division is also a field in my view > > When I do a query on these fields like this > > Select Division, Sum((Area1/Area2)*Area3) as ProratedArea > From table > Group by Division > > The Prorated Area looks different from what I have in the next Query > > Select Division, Sum((Convert(Numeric(20,2),Area1)/Area2)*Area3) as > ProratedArea > From table > Group by Division > > What is the reason for this difference? > The field Area is already numeric and why should I cast the related fields > again as numeric? > > Thanks > Baiju
From: Plamen Ratchev on 26 Jul 2010 14:52 Another reason could be the rounding of decimal places. See this example: DECLARE @x NUMERIC(20, 4) = 0.12345; SELECT @x, CAST(@x AS NUMERIC(20, 2)); ------- ---- 0.1235 0.12 -- Plamen Ratchev http://www.SQLStudio.com
From: Erland Sommarskog on 26 Jul 2010 16:21 Baiju K U (baiju(a)indus-systems.com) writes: > I have a table which has a column called 'Area' which is defined as > Numeric data type. > I have some calculation on this area field and I made a view which has > three fields related to Area. Let the fields be Area1, Area2 , Area3. > This area fields are mapped across various Divisions. > So Division is also a field in my view > > When I do a query on these fields like this > > Select Division, Sum((Area1/Area2)*Area3) as ProratedArea > From table > Group by Division > > The Prorated Area looks different from what I have in the next Query > > Select Division, Sum((Convert(Numeric(20,2),Area1)/Area2)*Area3) as > ProratedArea > From table > Group by Division > > What is the reason for this difference? > The field Area is already numeric and why should I cast the related fields > again as numeric? Because there is not one numeric data type in SQL Server, but over 600 of them, and now you are casting to another. The rules what happens when you divide and multiply numeric values with each other are not trivial, nor always obvious. To add insult to injury, SQL Server has a data type precedence, which means that if two different types meet, the one with lower priority will be converted to the other will be converted, if there is an implicit conversion. (If there is not, you will get an error.) In this case I would suggest that the best is do: convert(numeric(p, s), SUM(1E0*Area1/Area2*Area3)) where (p, s) is the desired precision and scale for the output. By adding 1E0, you are converting all values to float, which surely is the best data type for the calculation as such. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Baiju K U on 2 Aug 2010 04:49
Thanks for the input, Erland.. That was informative. I fixed it as you said.. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9DC1E3845EE41Yazorman(a)127.0.0.1... > Baiju K U (baiju(a)indus-systems.com) writes: >> I have a table which has a column called 'Area' which is defined as >> Numeric data type. >> I have some calculation on this area field and I made a view which has >> three fields related to Area. Let the fields be Area1, Area2 , Area3. >> This area fields are mapped across various Divisions. >> So Division is also a field in my view >> >> When I do a query on these fields like this >> >> Select Division, Sum((Area1/Area2)*Area3) as ProratedArea >> From table >> Group by Division >> >> The Prorated Area looks different from what I have in the next Query >> >> Select Division, Sum((Convert(Numeric(20,2),Area1)/Area2)*Area3) as >> ProratedArea >> From table >> Group by Division >> >> What is the reason for this difference? >> The field Area is already numeric and why should I cast the related >> fields >> again as numeric? > > Because there is not one numeric data type in SQL Server, but over 600 of > them, and now you are casting to another. > > The rules what happens when you divide and multiply numeric values with > each > other are not trivial, nor always obvious. To add insult to injury, SQL > Server has a data type precedence, which means that if two different types > meet, the one with lower priority will be converted to the other will be > converted, if there is an implicit conversion. (If there is not, you will > get an error.) > > In this case I would suggest that the best is do: > > convert(numeric(p, s), SUM(1E0*Area1/Area2*Area3)) > > where (p, s) is the desired precision and scale for the output. By adding > 1E0, you are converting all values to float, which surely is the best data > type for the calculation as such. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |