Recently I have been taking many interviews to increase work force in my companies outsourcing establishment. One question I ask to all interview candidates. What is Aggregate Function? So far I have received two different kind of response. First, I do not know. Second, AVG, SUM, COUNT are aggregate functions. The second response is good enough but not technically correct. None of the candidate have gave me good definition of Aggregate Function.
Definition from BOL is Aggregate functions perform a calculation on a set of values and return a single value.
Following functions are aggregate functions.
AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP
Aggregate functions ignores NULL values except COUNT function. HAVING clause is used, along with GROUP BY, for filtering query using aggregate values.
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
28 Comments. Leave new
Dear Pinal,
Keep it up, I am receiving alerts for your new posted articles.
It is helping me to update myself on daily basis.
Keep it up.
God Bless you.
Rana
How to Get alerts for pinal new posted articles.
Did you ever try to look the above right side of the blog?
Are you dumb?
I know this is pretty old post. But since I stumbled on it just now, I am putting my reaction out there.
That was needlessly harsh. It is possible that somebody is new to this idea of getting feed updates. Everybody begins somewhere.
I never realized that I have such a bad comment by someone. I agree with you @goodsammy1
Dear Pinal ,
U have made a good platform..
Regards
Faisal ahmed Qureshi
MES
Mumbai
if i want insert data to store before last data in a table
how can u explain
select * from user
name age
ajay 25
sad 35
now i need to inserting data to before max age 35
Ordering doesn’t matter as long as you use ORDER BY Clause in the SELECT statement
Hi all,
I have a small query regarding the usage of Aggregate functions with Union or Union All command. It would be great if som1 can help me out.
SELECT TYPE, DATEDIFF(HH,CREATE_DATE,END_DATE) AS DURATION
FROM TABLE1
WHERE TAG IN (‘A’,’B’,’F’,’X’)
UNION ALL
SELECT TYPE, DATEDIFF(HH,CREATE_DATE,END_DATE) AS DURATION
FROM TABLE2
WHERE TAG IN (‘C’,’J’)
Using the above code I am able to get the results from both the tables but I want the AVERAGE of the DURATION from the result set for a particular TYPE.
SELECT TYPE,avg(DURATION) FROM(
SELECT TYPE, DATEDIFF(HH,CREATE_DATE,END_DATE) AS DURATION
FROM TABLE1
WHERE TAG IN (‘A’,’B’,’F’,’X’)
UNION ALL
SELECT TYPE, DATEDIFF(HH,CREATE_DATE,END_DATE) AS DURATION
FROM TABLE2
WHERE TAG IN (‘C’,’J’)
) RESULT
Hi Pinal,
Your site is so great that i who was a person not interested in SQL have become keenly interested in SQL.
Hats off Man
Hi Pinal
Is there any way to list all the “System Funtions” from DB.
like min,max,round,Floor…. etc.
possible please send me the queries and syntax
Thanks a lot
Raj
Search for them in SQL Server help file
thanks for hark backing the whole sql server.
Hi Pinal!
I read almost all the post about how to find the Nth largest salary from a table. I want to create a function that takes two parameters
1. column name.
2. value of “n”.
and this return the nth highest salary. I googled it like a million and a half times but I reached nowhere.
I think this can be done using Aggregate functions. i want to know how to create aggregate function?? please help me out.
Make use of the methods described here
hello sir
i have a table salary
sal_salary int
sal_id int
sal_fund int
):
sal_salary
14000
12000
11000
9000
16000
18000
20,000
22,000
15000
12000
total 400 records in sal_salary column
if i want to sum full column used to
select sum(sal_salary) from salary
if i want to sum 200 to 300 column
then i dont know about answer this question
If I understand correctly, you are trying to sum multiple columns at the same time, right?
Here is a query from Northwind database that shows it is possible to do that:
select
SUM(productid) as sum1
,SUM(unitprice) as sum2
from dbo.[Order Details]
PLEASE GIVE happychhabra QUESTION ANSWER IF ANY ONE CAN ON MY EMAIL ALSO
please delete this i posted accidentaly
PS: thanks for a great site ;)
Hello sir,
How to create the width_bucket function in sql server 2005.
Could you please give me the syntax with example
Regards
Krishna
Hi,
please explain Checksum_AGG() function and where exactly we are going to use.
Thanks
Subbu
thanku sir your examples are very useful to me…
With regards
Rajitha
hi sir, can i have the doc on entire aggregate functions….
i want some questions on string functions to solve… where can i find it….
hello pinal,
how to find sum of a string columns values and a answer is set a string value in sql server 2008 r2
hello sir, i’ve this Warning
(Warning: Null value is eliminated by an aggregate or other SET operation)
what is the solution for this
Hiii pinal i need clear explanation about SQL Aggregate Functions like CHECKSUM_AGG
STDEV ,VAR ,VARp