Working with T-SQL function AVG ()

  • Comments posted to this topic are about the item Working with T-SQL function AVG ()

  • A major use case missed is use of the OVER clause:

     

    select

    id,

    name,

    designation,

    Salary,

    AVG(salary) over (partition by designation) Avg_Sal

    from [dbo].[Associates]

    would yield the entire list, along with the average salary for all with the same designation

  • It's nice to see articles on the basics of SQL server without it being a simple regurgitation of whats in the MS documentation and this is a nice one.  Thanks for taking the time to put it together and post it as an article.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks for this helpful review of the AVG() T-SQL function!

    I'd just like to call attention to the few minor typos in the article - just helpful feedback. For example

    "all female workers and make workers along with their respective average salary." << should be "male workers"

    SELECT name,

    city,

    age,

    gender,

    destination,  -- <<< should be designation

    salary

    FROM [dbo].[Associates]

    WHERE salary > (SELECT AVE(salary) FROM [dbo].[Associates]) -- << should be AVG

    As I said, though, these are minor. I compensated for them easily as I followed the article, but I think fixing them will polish the article nicely.

    Thanks again!

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Perhaps more important than the DISTINCT keyword is the AVG() function's handling of NULL values.

    NULLs behave similarly to DISTINCT - regardless of how AVG() is called. That is, the null tuple is excluded from the calculation altogether. It is not treated as a zero. For example, the AVG() of the following three rows:

    1

    NULL

    2

    is 1.5, ie (1+2)/2

    where AVG(ISNULL(RowValue, 0)) is (1+0+2)/3, which is 1.

     

     

     

  • Guy Stephens wrote:

    Perhaps more important than the DISTINCT keyword is the AVG() function's handling of NULL values.

    NULLs behave similarly to DISTINCT - regardless of how AVG() is called. That is, the null tuple is excluded from the calculation altogether. It is not treated as a zero. For example, the AVG() of the following three rows:

    1 NULL 2

    is 1.5, ie (1+2)/2

    where AVG(ISNULL(RowValue, 0)) is (1+0+2)/3, which is 1.

    I'm actually tickled that it works that way because, to me, NULL <> Nothing.  Of course, wars have been fought over that subject and so understand that's just my opinion and preference.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 6 (of 6 total)

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