Aggregate Functions in SQL Server

Discussion in 'SQL Server' started by MinalS, Oct 8, 2014.

  1. The aggregate functions are used for performing operation on a set of values. The result returned is a single value. The SELECT statement is used along with the function to perform the specific operations. The syntax for the aggregate function is as shown below:

    Code:
    SELECT aggregate_function ( [ ALL | DISTINCT ] ) expression )
    FROM table_name
    
    Where,
    • ALL is used to specify that the aggregate function is applied to all the values in a column.
    • DISTINCT is used to specify only the unique values in the specified column.
    • expression is used to specify the column or an expression with operators.
    • The return type is determined by the expression as int, bigint, float, decimal and money.
    The aggregate functions in SQL Server are as follows:
    1. Avg ()
    2. Sum()
    3. Count ()
    4. Min ()
    5. Max ()
    1. Avg()

    The function returns the average value as a result from the expression. The syntax for the average statement is as shown below:
    Code:
    AVG ( [ ALL | DISTINCT ] ) expression ) OVER
     ( [ partition_by_clause ] order_by_clause )
    
    Where,
    • ALL is the aggregate function applied to all the values.
    • DISTINCT is used to specify that the action is performed on unique values.
    • expression is the numeric or expression data type
    Consider the empdetails table containing the columns as mentioned below:

    [​IMG]

    The output for the table is as shown below:

    [​IMG]

    The query to demonstrate the average function on the table is as shown below:

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    2. Sum()

    The function is used to return the total sum of the numeric expression. The values can be either distinct or all. The syntax for demonstrating the sum statement is as shown below:

    Code:
    SELECT SUM ( column_name) FROM table_name;
    The query to demonstrate the SUM function is as shown below:

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    3. Count()

    The function is used to return the number of rows that is matching the criteria specified by the user. The syntax for demonstrating the count statement is as shown below:
    Code:
    SELECT COUNT ( column_name ) from table_name;
    
    If user wants all the records from the specific table, the following syntax is used.
    Code:
    SELECT COUNT ( * ) FROM table_name;
    
    If user wants only unique records from the table, the following syntax is used
    Code:
    SELECT COUNT ( DISTINCT column_name) from table_name;
    
    The query to demonstrate the count function is as shown below:

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    4. Min()

    The function is used to return the lowest value in the expression. The syntax for the min function is as shown below:
    Code:
    SELECT MIN ( column_name) FROM table_name;
    
    The query to demonstrate the function is as shown below:

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    5. Max()

    The function is used to return the maximum value in the expression. The syntax for the max function is as shown below:

    Code:
    SELECT MAX ( column_name) FROM table_name;
    
    The query to demonstrate the function is as shown below:

    [​IMG]

    The output is as shown below:

    [​IMG]

    SQL GROUP BY Statement



    The GROUP BY clause is used to summarize the data into groups. The SELECT statement is used to for defining the group by clause. The HAVING clause is used to produce the results based on the condition. The syntax for the GROUP BY clause is as shown below:

    Code:
    SELECT column_list FROM table_name
    WHERE condition 
    [ GROUP BY [ ALL ] expression [ , expression ]
    [ HAVING search_condition ]
    
    Where,
    • ALL is the keyword used to add groups that do not meet the condition.
    • expression is the column name on which the result set is grouped.
    • search_condition is the conditional expression on which the result is produced.
    The query to demonstrate the GROUP BY clause is as shown below:

    [​IMG]

    The output for the query is as shown below:

    [​IMG]

    COMPUTE and COMPUTE BY Statement



    The COMPUTE clause is used with the SELECT statement to produce the summary rows by using the aggregate function in the result. The COMPUTE BY clause is used to calculate the summary values of the result set on a group of data.

    The syntax for the COMPUTE and COMPUTE BY clause is as shown below:

    Code:
    SELECT column_list
    FROM table_name ORDER BY column_name
    COMPUTE aggregate_function ( column_name ) [ , aggregate_function  
    ( column_name ) …]
    [ BY column_name [ , column_name ] … ]
    
    Where,
    • ORDER BY column_name is the name of the column by which the result is stored.
    • COMPUTE aggregate_function specifies the row aggregate function from the function list
    • column_name is the name of the column for which the output is generated.
    • BY column_name is the name of the column by which the data is to be grouped.
    The query to demonstrate the COMPUTE statement is as shown below:

    [​IMG]

    The output for the query is as shown below:

    [​IMG]
     
    Last edited by a moderator: Jan 21, 2017
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice