July 1, 2022 at 12:00 am
Comments posted to this topic are about the item Working with T-SQL function AVG ()
July 1, 2022 at 11:52 am
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
July 1, 2022 at 5:10 pm
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
Change is inevitable... Change for the better is not.
July 1, 2022 at 7:05 pm
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
July 4, 2022 at 5:18 am
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.
July 4, 2022 at 5:52 pm
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply