SQL SERVER – Introduction to Aggregate Functions « Journey to SQL Authority with Pinal Dave

Feeds:: Posts; Comments

SQL SERVER – Introduction to Aggregate Functions

January 19, 2008 by pinaldave

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 (http://blog.SQLAuthority.com) , BOL

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Interview Questions and Answers, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology | 8 Comments

8 Responses

on January 21, 2008 at 5:46 pm | Reply SQL SERVER - Introduction to Statistical Functions - VAR, STDEVP, STDEV, VARP Journey to SQL Authority with Pinal Dave

[...] 20, 2008 by pinaldave Yesterday I wrote article about SQL SERVER – Introduction to Aggregate Functions. I received one email that four of the aggregate functions are statistical function and I should [...]
on January 22, 2008 at 11:24 am | Reply Rana

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
on January 22, 2008 at 4:10 pm | Reply Faisal Ahmed

Dear Pinal ,

U have made a good platform..

Regards
Faisal ahmed Qureshi

MES
Mumbai
on September 20, 2008 at 3:15 pm | Reply basky

if i want insert data to store before last data in a table
how can u explain
on September 20, 2008 at 3:26 pm | Reply basky

select * from user

name age
ajay 25
sad 35

now i need to inserting data to before max age 35
on October 14, 2008 at 12:00 am | Reply Sazz

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.
on February 26, 2009 at 5:38 pm | Reply SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

[...] MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP (Read More Here [...]
on July 10, 2009 at 6:16 pm | Reply prabhu

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