Working with T-SQL function AVG ()

Question

Post reply

Working with T-SQL function AVG ()

Manvendra Deo Singh

Old Hand

Points: 316
More actions
July 1, 2022 at 12:00 am

#4052396

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

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

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

Larry Peck Mr or Mrs. 500 Points: 501 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 2

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)

webrunner SSC-Dedicated Points: 31848 More actions · Answer 3

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

Guy Stephens Old Hand Points: 308 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 5

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)