Answered by:
decimals returned with AVG command

we're using the AVG function on a column defined as numeric(5,4). The result has 6 decimal positions and we needed it to be 8 decimal positions. We figured out how to get the 8 decimal positions (using convert), but i was wondering how it determined the number of decimal positions returned with the AVG function. Why was it setting it to 6 positions? Thanks
Question
Answers

hi
AVG of decimal always returns a "decimal(38, s) divided by decimal(10, 0)" data type table on link (see here)
You have to cast the AVG result to the desired precision.
Thanks
Saravana Kumar C
You have to cast the AVG() input to the desired precision, not the result if you want the scale of the result to be more than 6. If you cast the result of an AVG() of a decimal(5,4) to decimal(9,8), the value will have already been truncated to 6 decimal places in the AVG() function and casting it decimal(9,8) will just append two 0's in the 7th and 8th decimal place. To see this, run
Declare @T Table(d decimal(5,4)); Insert @T(d) Values (0), (0), (1); Select Cast(Avg(d) As decimal(9,8)) From @T; Select Avg(Cast(d As decimal(9,8))) From @T;
Only the second select returns the correct result to 8 decimal places.
sqlguy10, the rules for the scale and precision of a decimal calculation are complex. You can find some of them at http://technet.microsoft.com/enus/library/ms190476.aspx. Unfortunately, the documentation is incomplete and in some cases, the documentation is incorrect. In particular, the documentation for the AVG function is incorrect. The actual rule used by SQL for the result of an AVG of a decimal(p,s) expression is if s < 6, the result will be decimal(38,6) and if s >= 6, the result will be decimal(38,s).
If you are not sure of the datatype that is returned as the result of a calculation, you can always put the result into a sql_variant datatype and then use the SQL_VARIANT_PROPERTY function to tell you the datatype, precision, scale, maxlength, collation and totalbytes of the result. For example
Declare @T Table(d decimal(5, 4)); Declare @Result sql_variant; Insert @T(d) Values (0), (0), (1); Select @Result = Avg(d) From @T; Select @Result, SQL_VARIANT_PROPERTY(@Result, 'BaseType') As BaseType, SQL_VARIANT_PROPERTY(@Result, 'Precision') As Precision, SQL_VARIANT_PROPERTY(@Result, 'Scale') As Scale, SQL_VARIANT_PROPERTY(@Result, 'TotalBytes') As TotalBytes, SQL_VARIANT_PROPERTY(@Result, 'Collation') As Collation, SQL_VARIANT_PROPERTY(@Result, 'MaxLength') As MaxLength;
Tom Proposed as answer by Saeid Hasani Saturday, October 12, 2013 4:45 AM
 Marked as answer by Allen Li  MSFTModerator Monday, October 21, 2013 8:32 AM
All replies

hi
AVG of decimal always returns a "decimal(38, s) divided by decimal(10, 0)" data type table on link (see here)
You have to cast the AVG result to the desired precision.
Thanks
Saravana Kumar C
You have to cast the AVG() input to the desired precision, not the result if you want the scale of the result to be more than 6. If you cast the result of an AVG() of a decimal(5,4) to decimal(9,8), the value will have already been truncated to 6 decimal places in the AVG() function and casting it decimal(9,8) will just append two 0's in the 7th and 8th decimal place. To see this, run
Declare @T Table(d decimal(5,4)); Insert @T(d) Values (0), (0), (1); Select Cast(Avg(d) As decimal(9,8)) From @T; Select Avg(Cast(d As decimal(9,8))) From @T;
Only the second select returns the correct result to 8 decimal places.
sqlguy10, the rules for the scale and precision of a decimal calculation are complex. You can find some of them at http://technet.microsoft.com/enus/library/ms190476.aspx. Unfortunately, the documentation is incomplete and in some cases, the documentation is incorrect. In particular, the documentation for the AVG function is incorrect. The actual rule used by SQL for the result of an AVG of a decimal(p,s) expression is if s < 6, the result will be decimal(38,6) and if s >= 6, the result will be decimal(38,s).
If you are not sure of the datatype that is returned as the result of a calculation, you can always put the result into a sql_variant datatype and then use the SQL_VARIANT_PROPERTY function to tell you the datatype, precision, scale, maxlength, collation and totalbytes of the result. For example
Declare @T Table(d decimal(5, 4)); Declare @Result sql_variant; Insert @T(d) Values (0), (0), (1); Select @Result = Avg(d) From @T; Select @Result, SQL_VARIANT_PROPERTY(@Result, 'BaseType') As BaseType, SQL_VARIANT_PROPERTY(@Result, 'Precision') As Precision, SQL_VARIANT_PROPERTY(@Result, 'Scale') As Scale, SQL_VARIANT_PROPERTY(@Result, 'TotalBytes') As TotalBytes, SQL_VARIANT_PROPERTY(@Result, 'Collation') As Collation, SQL_VARIANT_PROPERTY(@Result, 'MaxLength') As MaxLength;
Tom Proposed as answer by Saeid Hasani Saturday, October 12, 2013 4:45 AM
 Marked as answer by Allen Li  MSFTModerator Monday, October 21, 2013 8:32 AM


Because that is how Microsoft decided to do it. Truncation and rounding are implementation defined in the ANSI/ISO Standards. DB2 used to give four decimals, Oracle has something huge. You need to use CAST() to adjust the results.
CELKO Books in Celko Series for MorganKaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL