SQL Aggregate Functions

SQL Aggregate Functions

SQL aggregate functions are also known as group functions. Group functions are built-in SQL functions that operate on groups of rows and return a single value for the entire group. We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.

There are five aggregate functions in sql.
  1. SUM - gives the sum of values
  2. AVG - gives the average of set of values
  3. MIN - finds minimum value from a set of values
  4. MAX - finds the maximum value from a given set of values
  5. COUNT - counts the rows in a specified table or view
Note: All aggregate functions above ignore NULL values except for the COUNT function.

Aggregate Functions Calling Syntax

To call an aggregate function in an sql query we need to use the following syntax:
aggregate_function (DISTINCT | ALL expression)
aggregate_function - the group function that you want to use (SUM, AVG, MIN, COUNT, MAX).
(DISTINCT | ALL expression) - second part of syntax for aggregate function is a modifier followed by an expression. If DISTINCT modifier is used the aggregate function ignores the duplicate values and takes only unique values. If you use ALL modifier aggregate function uses all values for calculation. ALL modifier is used by default if you do not specify one.

To understand all of the above aggregate functions, consider demo database with employee table as given below
-----+-------------+---------
 eno |    dept     | salary  
-----+-------------+---------
   1 | Science     | 1000.98
   2 | Mathematics | 3000.98
   3 | Physics     | 2170.89
   4 | Chemistry   | 3170.89
   5 | Biology     | 4170.89
-----+-------------+---------

SUM():

To get the sum of a numeric column this function is used.

To get the total salary given to employees, the query would be:
SELECT SUM (salary) FROM employee;

Output:

postgres=> select sum(salary) FROM employee;
        sum         
--------------------
 13514.630
(1 row)

AVG():

This function is used to get the average value of a numeric column.

To get the average salary, the query would be:
SELECT AVG (salary) FROM employee;

Output:

postgres=> select avg(salary) from employee;
        avg         
--------------------
 2702.9260000000004
(1 row)

MIN():

This function is used to return the minimum value from a column.

To find the minimum salary an employee gets, the query would be:
SELECT MIN (salary) FROM employee;

Output:

postgres=> select min(salary) from employee;
   min   
---------
 1000.98
(1 row)

MAX():

This function is used to get the maximum value from a column.

To get the maximum salary given to an employee, the query would be:
SELECT MAX (salary) FROM employee;

Output:

postgres=> select max(salary) from employee;
   max   
---------
 4170.89
(1 row)

COUNT():

This function returns the number of rows in the table that satisfies the condition specified in the WHERE clause. If condition is not specified in the WHERE clause, then the query returns the total number of rows in the table.

Example: If you want the number of employees whose salary is less than 1500, the query would be
SELECT COUNT (*) FROM employee 
WHERE salary < 1500;

Output:

postgres=> select count(*) from employee where salary < 1500;
 count 
-------
     1
(1 row)
If you want the total number of employees working, the query would be
SELECT COUNT (*) FROM employee;

Output:

postgres=> select count(*) from employee;
 count 
-------
     5
(1 row)

DISTINCT

This modifier is used to select the distinct rows.

Example: If you want to select all distinct department names from employee table, the query would be:
SELECT DISTINCT dept FROM employee;

Output:

postgres=> select DISTINCT dept from employee;
    dept     
-------------
 Chemistry
 Mathematics
 Physics
(3 rows)
Prev - SQL ORDER BY Clause Next - SQL GROUP BY Clause

Comments