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.
SELECT <FUNCTION NAME> (<PARAMETER>) FROM <TABLE NAME>
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
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;
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.
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
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
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
The VARIANCE Function is used to find variance of the column specified as argument.
Select VARIANCE(salary) from Employee