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

Previous Post
SQL SERVER – 2005 Best Practices Analyzer (January 2008)
Next Post
SQL SERVER – Introduction to Statistical Functions – VAR, STDEVP, STDEV, VARP

Related Posts

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

    Reply
    • How to Get alerts for pinal new posted articles.

      Reply
      • Did you ever try to look the above right side of the blog?
        Are you dumb?

      • goodsammy (@goodsammy1)
        March 2, 2017 9:42 pm

        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

    Reply
  • if i want insert data to store before last data in a table
    how can u explain

    Reply
  • select * from user

    name age
    ajay 25
    sad 35

    now i need to inserting data to before max age 35

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

    Reply
    • 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

      Reply
  • 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

    Reply
  • 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

    Reply
  • thanks for hark backing the whole sql server.

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

    Reply
  • 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

    Reply
  • 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]

    Reply
  • shravan yadav
    April 4, 2011 10:25 am

    PLEASE GIVE happychhabra QUESTION ANSWER IF ANY ONE CAN ON MY EMAIL ALSO

    Reply
  • please delete this i posted accidentaly

    PS: thanks for a great site ;)

    Reply
  • Hello sir,

    How to create the width_bucket function in sql server 2005.

    Could you please give me the syntax with example

    Regards
    Krishna

    Reply
  • Hi,

    please explain Checksum_AGG() function and where exactly we are going to use.

    Thanks
    Subbu

    Reply
  • thanku sir your examples are very useful to me…
    With regards
    Rajitha

    Reply
  • 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….

    Reply
  • hello pinal,

    how to find sum of a string columns values and a answer is set a string value in sql server 2008 r2

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

    Reply
  • Spandana daram
    July 16, 2013 12:18 pm

    Hiii pinal i need clear explanation about SQL Aggregate Functions like CHECKSUM_AGG
    STDEV ,VAR ,VARp

    Reply

Leave a Reply

Menu