Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Unanswered: CAST or CONVERT When Calc'ing an AVG from INTs?

    Just a general question here. I have an INT field that I want to take the average value of, to the 2nd decimal. Is there a difference between using CAST vs CONVERT? Maybe I am over complicating this...

    CAST
    Code:
    CAST(Avg(CAST(units AS DECIMAL(10,2))) AS DECIMAL(10,2))
    from myTable
    CONVERT
    Code:
    CONVERT(numeric(10,2),avg(CONVERT(numeric(10,2),units))) 
    from myTable

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There's not much different between Cast and Convert. For your current purpose they are identical.

    The Cast() function is the ANSI standard function.
    The proprietary Convert() function has a 3rd optional parameter you can pass to it to "format" certain data types (e.g. dates).

    Incidentally, you don't need to perform the action twice:
    Code:
    SELECT Avg(Cast(units As decimal(10,2)) As casted
         , Avg(Convert(decimal(10,2), units)) As converted
    FROM   your_table
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by gvee View Post
    There's not much different between Cast and Convert. For your current purpose they are identical.

    The Cast() function is the ANSI standard function.
    The proprietary Convert() function has a 3rd optional parameter you can pass to it to "format" certain data types (e.g. dates).

    Incidentally, you don't need to perform the action twice:
    Code:
    SELECT Avg(Cast(units As decimal(10,2)) As casted
         , Avg(Convert(decimal(10,2), units)) As converted
    FROM   your_table
    Thanks for the info, but if i don't CAST/CONVERT twice, then the results are not rounded to 2 decimals. The above formulas both output 3.390089 instead of the desired 3.39.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is REALLY hard to compute averages at a specific level of precision using Transact-SQL because the engine itself coerces DECIMAL data before the computation.
    Code:
    DECLARE @v1 sql_variant
    ,  @v2 sql_variant
    
    --  Cast Integers to DECIMAL as a test
    
    SELECT @v1 = Avg(Cast(id AS Decimal(20, 2)))
    ,  @v2 = Avg(Convert(DECIMAL(20, 2), id))
       FROM sysobjects
    
    --  This confirms http://technet.microsoft.com/en-us/library/ms187810.aspx
    
    SELECT '@v1' AS varname, @v1 AS value
    ,  SQL_Variant_Property(@v1, 'BaseType') AS Data_Type
    ,  SQL_Variant_Property(@v1, 'Precision') AS Precision
    ,  SQL_Variant_Property(@v1, 'Scale') AS scale
    UNION SELECT '@v2' AS varname, @v2 AS value
    ,  SQL_Variant_Property(@v2, 'BaseType') AS Data_Type
    ,  SQL_Variant_Property(@v2, 'Precision') AS Precision
    ,  SQL_Variant_Property(@v2, 'Scale') AS scale
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    to add to that ..

    The precision and rounding of aggregate functions is implementation defined in the ANSI/ISO standards. Everyone is different.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •