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.
Common Mistakes With Aggregate Functions
Knowing the list is a good start, but the questions I really like to ask are about how these functions behave. Here are the points I see people miss most often:
COUNT(*)counts every row, including rows with NULL.COUNT(ColumnName)counts only the rows where that column is not NULL. On the same table they can return different numbers.AVGon an integer column returns an integer, so the decimal part is lost. UseAVG(CAST(Col AS DECIMAL(10,2)))when you need it.SUMover rows that are all NULL returns NULL, not 0. Wrap it inISNULLif a report needs a zero.COUNT(DISTINCT Col)counts unique values only, which is often what a business question really asks for.
The other classic mistake is mixing up WHERE and HAVING. WHERE filters rows before they are grouped, and HAVING filters the groups after the totals are calculated. If a condition does not use an aggregate, put it in WHERE, so the query does less work. Also remember that every column in the SELECT list that is not inside an aggregate must be in the GROUP BY clause. When I ask about these in interviews, a candidate who explains one of them with a small example impresses me more than one who recites the whole list.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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