Aggregate Functions in DBMS

DatabaseMCA

Aggregate functions in DBMS take multiple rows from the table and return a value according to the query.

All the aggregate functions are used in Select statement.

Syntax − 

SELECT <FUNCTION NAME> (<PARAMETER>) FROM <TABLE NAME>

AVG Function

This function returns the average value of the numeric column that is supplied as a parameter.

Example: Write a query to select average salary from employee table.

Select AVG(salary) from Employee

COUNT Function

The count function returns the number of rows in the result. It does not count the null values.

Example: Write a query to return number of rows where salary > 20000.

Select COUNT(*) from Employee where Salary > 20000;

Types −

  • COUNT(*): Counts all the number of rows of the table including null.

  • COUNT( COLUMN_NAME): count number of non-null values in column.

  • COUNT( DISTINCT COLUMN_NAME): count number of distinct values in a column.

MAX Function

The MAX function is used to find maximum value in the column that is supplied as a parameter. It can be used on any type of data.

Example − Write a query to find the maximum salary in employee table.

Select MAX(salary) from Employee

SUM Function

This function sums up the values in the column supplied as a parameter.

Example: Write a query to get the total salary of employees.

Select SUM(salary) from Employee

STDDEV Function

The STDDEV function is used to find standard deviation of the column specified as argument.

Example − Write a query to find standard deviation of salary in Employee table.

Select STDDEV(salary) from Employee

VARIANCE Function

The VARIANCE Function is used to find variance of the column specified as argument.

Example −

Select VARIANCE(salary) from Employee
raja
Published on 25-Jul-2018 07:19:33
Advertisements