Aggregate Functions in DBMS
Aggregate Functions in DBMS: Aggregate functions are those functions in the DBMS which takes the values of multiple rows of a single column and then form a single value by using a query. These functions allow the user to summarizing the data. These functions ignore the NULL values except the count function.
In Database Management System, following are the five aggregate functions:
1. AVG
2. COUNT
3. SUM
4. MIN
5. MAX
AVG Function
This function takes the values from the given column and then returns the average of the values. This function works only on the datatypes, which are specified as numeric in the table.
Let's take an example, which describes to you how to use the AVG function in SQL. Suppose we want to calculate the average salary from the Employee_Details table, then we have to type the following query:
Select AVG(Employee_salary) from Employee_Details;
COUNT Function
This aggregate function returns the total number of values in the specified column. This function can work on any type of data, i.e., numeric as well as non-numeric. This function does not count the NULL values. If we want to count all the rows with NULL values, then we have to use the Count(*) function.
Let's take an example, which describes to us how to use the COUNT function in SQL. Suppose a user wants to count the number of employees in the Employee_Details table, then we have to type the following query:
Select Count(Employee_ID) from Employee_Details;
SUM Function
This aggregate function sums all the non-NULL values of the given column. Like the AVG function, this function also works only on the numeric data.
Let's take an example, which describes to you how to use the SUM function in SQL. Suppose a user wants to find the sum of salary from the Employee_Details table, then we have to type the following query:
Select SUM(Employee_salary) from Employee_Details;
MAX Function
This function returns the value, which is maximum from the specified column.
Let's take an example, which describes to you how to use the MAX function in SQL. Suppose we want to find the maximum price of the Cars_Price column from the Cars table, then we have to type the following query:
Select MAX(Cars_Price) from Cars;
MIN Function
This function returns the value, which is minimum from the specified column.
Let's take an example, which describes to you how to use the MIN function in SQL. Suppose we want to find the minimum price of the Bikes_Price column from the Bikes table, then we have to type the following query:
Select MIN(Bikes_Price) from Bikes;