Aggregate Functions or Group Functions - (Avg,Count,Max,Min,Sum)

1) AVG

This command is used to retrieve average.

Example

SELECT AVG(SALARY) FROM EMPLOYEES;

  AVG(SAL)
----------
15142.0588

2) COUNT

This command is used to display the count.

COUNT (COLUMN_NAME) – displays number of not null column in particular column.
COUNT (*) – displays number of row in the table without eliminating null columns or rows.

Example

SELECT COUNT(SALARY) FROM EMPLOYEES;

COUNT(SALARY)
-------------
          107

  COUNT(*)
----------
       107

3) MAX

This command is used to retrieve maximum number or salary.

Example

SELECT MAX(SALARY) FROM EMPLOYEES;

MAX(SALARY)
-----------
      24000

4) MIN

This command is used to retrieve minimum number or salary.

Example

SELECT MIN(SALARY) FROM EMPLOYEES;

MIN(SALARY)
-----------
       2100

5) SUM

This command to retrieve by summing up all the rows in particular column. It works only if the column data type is number.

Example

SELECT SUM(SALARY) FROM EMPLOYEES;

SUM(SALARY)
-----------
  691633.75

There are certain rules for writing aggregate functions.

i) Group functions accept only one argument.
ii) Group functions ignore null values including COUNT(*)
iii) Whenever column is followed by group function or group function followed by column should have ‘group by’
iv) These group functions SUM & AVG only work in number and not in date and character.
v) The ‘*’ cannot be used except COUNT.
vi) When you are restricting with group functions use ‘HAVING’ clause.