Let’s learn about SQL aggregate function in this article. Requirement for Aggregrate function is that a data should be grouped into some sort of group. This function produces records to retrieve one value for whole group and if record is grouped by group clause than only one value will be returned for each group. Also if group by clause is not used than whole table groups into one value as a result of aggregrate function.
Aggregrate functions are listed below:
- Count() : it return total number of record in a table
- SUM() : it return the total sum in a column
- MIN() : it return the minimum value in a column
- MAX() :it return the maximum value in a column
- AVG() : it return the average of value in a column
Now let’s see the usage of above aggregate functions in detail:
- It takes input in bracket and return total number of record in a table
- It does not count the null values
- In following example we are returning the total record in salary table
- This function will treate salary table into one group and return one value from it
- Consider the following table salary:
- Let’s fire an sql query on this table:
Sql-query: select count (*) from salary
Output: 7 (It will count the total number of row in a table without including null value)
- It return’s the total sum in a column
- It return’s the sum of numeric column
- Consider following salary table:
- In the above example we are having totally disturbed salary for each department
Sql query: select sum(monthlysalary)as totalsalary, department from salary group by department order by totalsalary
Output: Above query resulted into total salary by each department group.
- Example 1:In this example we have fired query to find the largest amount of salary column from salary table
- Example 2:To find highest salary in each department we can use group by clause in department column
Sql-query: select max(monthlysalary) as higestsalary from salary
- Example : To get minimum salary from salary table.
Sql-query: select min(monthlysalary) as minimum from salary
- Example 1: To get average salary disbursed from salary table
Sql-query: select avg(monthlysalary) as average from salary
Thus, we learnt SQL aggregate functions in detail with examples.