SQL SERVER – Introduction to Aggregate Functions

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

About these ads

28 thoughts on “SQL SERVER – Introduction to Aggregate Functions

  1. Pingback: SQL SERVER - Introduction to Statistical Functions - VAR, STDEVP, STDEV, VARP Journey to SQL Authority with Pinal Dave

  2. 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

  3. 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

  4. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

  5. 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

  6. 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

  7. 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.

  8. 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

  9. 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]

  10. Hello sir,

    How to create the width_bucket function in sql server 2005.

    Could you please give me the syntax with example

    Regards
    Krishna

  11. 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….

  12. hello sir, i’ve this Warning
    (Warning: Null value is eliminated by an aggregate or other SET operation)
    what is the solution for this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s