You are here
Home > Sql Server > Aggregate functions in sql

Aggregate functions in sql

Aggregate functions

  • Aggregate function is a function that you apply to a set of rows and get a single value back.
  • It ignore NULL inputs when applies to an expression.
  • SQL supports aggregate functions such as COUNT, MIN, MAX, SUM, AVG and others.
  • If you want to apply an aggregate function to distinct values, then add the DISTINCT clause.

 

COUNT ( ) :

  • It returns the number of rows in the table, including duplicates.
  • Syntax: COUNT(exp);

 

COUNT_BIG( ):

  • It is used to return the row count as BIGINT value.
  • Syntax: COUNT_BIG(exp);

 

AVG ( ):

  • It returns the average of the values in a group.
  • It ignores NULL values.
  • Syntax: AVG (exp);

 

SUM ( ):

  • It returns the sum of all the values or only the distinct value in the expression.
  • Syntax: SUM(exp);

 

MAX ( ):

  • It returns the maximum value in the expression.
  • Syntax: MAX(exp);

 

MIN ( ):

  • It returns the minimum value in the expression.
  • Syntax: MIN(exp);

 

 

SQRT ( ):

  • It returns the square root of the specified value.
  • Syntax: SQRT(float exp);
  • Example: SELECT SQRT(64) AS SQUARE_ROOT;
  • Output:
  • SQUARE_ROOT

    8

 

 

 

 

 

Example:

Table: EMP

ID

NAME CITY SALARY

1

KRISHNA

ANAND 2000

2

KRISHNA V.V.NAGAR 4000

3

ATUL

ANAND

4000

4 ATUL AHMEDABAD

8000

 

 

 COUNT():

 

  • SELECT COUNT(*) AS COUNT FROM EMP;
  • Output:

    COUNT

    4

 

 

  • SELECT COUNT(1) AS COUNT FROM EMP;
  • Output:

    COUNT

    4

 

 

  • SELECT COUNT(CITY) AS CITY FROM EMP;
  • Output:

    CITY

    4

     

 

 

  • SELECT COUNT(DISTINCT CITY) AS CITY FROM EMP;
  • Output:

    CITY

    3

     

 

COUNT_BIG():

 

  • SELECT COUNT_BIG(NAME) AS NAME FROM EMP;
  • Output:

    NAME

    4

 

  • SELECT COUNT_BIG(DISTINCT NAME) AS NAME FROM EMP;
  • Output:

    NAME

    2

 

AVG():

 

  • SELECT AVG(SALARY) AS AVGSALARY FROM EMP;
  • Output:

    AVGSALARY

    4500

     

  • SELECT AVG(DISTINCT SALARY) AS AVGSALARY FROM EMP;
  • Output:

    AVGSALARY

    4666

     

 

SUM():

 

  • SELECT SUM(SALARY) AS TOTALSALARY FROM EMP;
  • Output:

    TOTALSALARY

    18000

     

 

  • SELECT SUM(DISTINCT SALARY) AS TOTALSALARY FROM EMP;
  • Output:

    TOTALSALARY

    14000

     

MAX():

 

  • SELECT MAX(SALARY) AS MAXSALARY FROM EMP;
  • Output:

    MAXSALARY

    8000

     

 

MIN():

 

  • SELECT MIN(SALARY) AS MINSALARY FROM EMP;
  • Output:
    MINSALARY

    2000

     

 

 

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

Leave a Reply

Top