Aggregate Functions



Aggregate Functions in SQL Server


Aggregate function in SQL is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.

Types of Aggregate functions in SQL:


  • AVG - calculates the average of a set of values.

  • COUNT - counts rows in a specified table or view.

  • MIN - gets the minimum value in a set of values.

  • MAX - gets the maximum value in a set of values.

  • SUM - calculates the sum of values.

    1.AVG Function

    AVG function in SQL is used to find out the average of a field in various records.

    Syntax

    AVG()  
    or  
    AVG( [ALL|DISTINCT] expression )  
    
    Example
    ProductCompanyQuantityRateCost
    Item1Com121020
    Item2Com231545
    Item3Com2420100
    Item4Com315050
    Item5Com3735245

    Let us use the same table for checking out all the functions:


    Now,we will check how average function works

    SELECT AVG(COST)  
    FROM PRODUCT_MAST;  
    

    Output :92

    2.COUNT Function

    COUNT function in SQL is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.

    COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.

    Syntax
    COUNT(*)  
    or  
    COUNT( [ALL|DISTINCT] expression )  
    
    Example: COUNT()
    SELECT COUNT(*)  
    FROM PRODUCT_MAST;  
    

    Output :5

    Example: COUNT with WHERE
    SELECT COUNT(*)  
    FROM PRODUCT_MAST;  
    WHERE RATE>=20;  
    

    Output :4

    Example: COUNT() with GROUP BY
    SELECT COMPANY, COUNT(*)  
    FROM PRODUCT_MAST  
    GROUP BY COMPANY;  
    

    Output :

    Com1    1
    Com2    2
    Com3    2
    
    Example: COUNT() with HAVING
    SELECT COMPANY, COUNT(*)  
    FROM PRODUCT_MAST  
    GROUP BY COMPANY  
    HAVING COUNT(*)>2;  
    

    Output :

    Com1    1
    Com2    2
    

    3.MIN Function

    MIN function in SQL is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

    Syntax
    MIN()  
    or  
    MIN( [ALL|DISTINCT] expression ) 
    

    Example

    SELECT MIN(RATE)  
    FROM PRODUCT_MAST;  
    

    Output :10


    4.MAX Function

    MAX function in SQL is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

    Syntax
    MAX()  
    or  
    MAX( [ALL|DISTINCT] expression )  
    

    Example

    SELECT MAX(RATE)  
    FROM PRODUCT_MAST;  
    

    Output :50

    5.SUM Function

    Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

    Syntax

    SUM()  
    or  
    SUM( [ALL|DISTINCT] expression )  
    

    Example: SUM()

    SELECT SUM(COST)  
    FROM PRODUCT_MAST; 
    

    Output :460

    Example: SUM() with WHERE

    SELECT SUM(COST)  
    FROM PRODUCT_MAST  
    WHERE QTY>3;  
    

    Output :345

    Example: SUM() with GROUP BY
    SELECT SUM(COST)  
    FROM PRODUCT_MAST  
    WHERE QTY>3  
    GROUP BY COMPANY;  
    

    Output :

    Com2    100
    Com3    245
    

    Connection String

    SQL Server Connection String Formats

    posted on 2019-08-27 05:53:53 - SQL Server Tutorials


    UNION

    union in SQL server

    posted on 2019-08-09 23:05:33 - SQL Server Tutorials


    System Functions

    System Functions in SQL Server

    posted on 2019-08-09 06:14:33 - SQL Server Tutorials


    Prompt Examples

    ChatGPT Prompt Examples

    posted on 2023-06-21 22:37:19 - ChatGPT Tutorials


    Use Cases

    Chat GPT Key Use Cases

    posted on 2023-06-21 21:03:17 - ChatGPT Tutorials


    Prompt Frameworks

    Prompt Frameworks

    posted on 2023-06-21 19:33:06 - ChatGPT Tutorials