none
decimals returned with AVG command

    Question

  • 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

    Friday, October 11, 2013 9:58 PM

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/en-us/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
    Saturday, October 12, 2013 4:09 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



    • Edited by SaravanaC Saturday, October 12, 2013 12:11 AM
    Saturday, October 12, 2013 12:10 AM
  • 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/en-us/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
    Saturday, October 12, 2013 4:09 AM
  • Hi Sqlguy10,

    You have to put more decimal positions for having higher accuracy. See this example

    select cast(cast(31 as decimal(12,6))/cast(9987 as decimal(18,6)) as decimal(18,6))
    go
    



    Many Thanks & Best Regards, Hua Min

    Saturday, October 12, 2013 8:09 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 Morgan-Kaufmann 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

    Sunday, October 13, 2013 3:46 PM