Post reply

AVG function

  • June 21, 2011 at 7:32 pm

    #1342199

    What if you change it to:

    declare @test-2 table ( d1 int )

    insert into @test-2 values ( 5 )

    insert into @test-2 values ( 15 )

    insert into @test-2 values ( 0 )

    insert into @test-2 values ( 7 )

    select AVG(d1) from @test-2

    Still 6 since it rounds DOWN or truncates the decimal. I never liked that aspect of the AVG function when working with int. Shows the importance of knowing the datatype you are working with and the effects of the functions you use on them. You might be surprised how much code I have to fix like this. I thought this was fairly well known and I am no code monkey 🙂

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • June 22, 2011 at 12:04 am

    #1342239

    Too easy... 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 22, 2011 at 12:15 am

    #1342240

    As Mike said the question would have been little tricky if one or two rows contains null. As we know except for COUNT, aggregate functions ignore null values.

  • June 22, 2011 at 12:04 pm

    #1342637

    Anipaul (6/22/2011)

    As Mike said the question would have been little tricky if one or two rows contains null. As we know except for COUNT, aggregate functions ignore null values.

    COUNT behaves consistently with other aggregate functions. It does have the special feature of being able to include NULL values by specifying Count(*). Try this script to see what I mean.

    declare @test-2 table ( d1 int )

    insert into @test-2 values ( 5 )

    insert into @test-2 values ( 15 )

    insert into @test-2 values ( 0 )

    insert into @test-2 values ( 5 )

    insert into @test-2 values (NULL) --<== a fifth row in the table

    select Count(d1) from @test-2 --<== returns 4

    select Count(*) from @test-2 --<== returns 5

  • June 22, 2011 at 1:12 pm

    #1342673

    Nice straighforward question. Thanks.

    Tom

  • June 23, 2011 at 7:24 pm

    #1343449

    Easy Question but great discussion about count(*) & inserting Null and also bit of knowledge about avg function from peter.

  • July 1, 2011 at 8:16 am

    #1347042

    if you change the data type of d1 to decimal then answer will be change.

  • July 20, 2011 at 10:31 am

    #1355843

    Does anyone else have an issue with this behavior? It seems to me that anyone asking for an AVG is asking for the result of division, and therefore the expects fractions, regardless of the input set. To me, I should have to ask explicitly for an integer result of an avg. Avg should be a float unless I specify otherwise.

  • July 20, 2011 at 11:56 am

    #1355917

    erickson55345 25302 (7/20/2011)

    Does anyone else have an issue with this behavior? It seems to me that anyone asking for an AVG is asking for the result of division, and therefore the expects fractions, regardless of the input set. To me, I should have to ask explicitly for an integer result of an avg. Avg should be a float unless I specify otherwise.

    I suppose that it is possible to make the case that you are asking for a rational - but the rationals are not represented in SQL, so it can't deliver that (neither floats nor exact numerics can deliver simple rational values like 1/3). I reckon that by asking for the average of a set of integers, given the constraint that an exact rational data type is not available and knowing that the integer result is exactly correct because the divide operation used is integer divide (not only is rational divide not available, but you can't even coerce the integers you started with to rational because you have no such type in the language) you are asking for an integer response.

    Of course there will be times when you want a good approximation to the result of doing the maths trick of injecting the ints to the rationals and doing the divide there, but that is easy to get by doing the conversion to exact numeric or to float before doing the division [for example by writing "....cast(SUM(i) as float(53))/cast(count_big(ALL,i) as float(53))..." in place of "....AVG(i)...."]. There is a very good reason for the system not to do that of its own accord, as you appear to be suggesting, when the code-writer doesn't specify it: if the system did that it would be very difficult indeed to get the exact integer-divide result, in particular the "obvious" (but actually wrong) method would sometimes deliver the wrong result (be out by 1).

    Tom

  • January 28, 2013 at 4:27 am

    #1581542

    thanks for question

    Manikandanps

    -----Go as fast as possiblge--------

    Manik
    You cannot get to the top by sitting on your bottom.

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply