What is SQL Aggregate function

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:

1. Count() : it return total number of record in a table
2. SUM() : it return the total sum in a column
3. MIN() : it return the minimum value in a column
4. MAX() :it return the maximum value in a column
5. AVG() : it return the average of value in a column

Now let’s see the usage of above aggregate functions in detail:

• Count():
  1. It takes input in bracket and return total number of record in a table
  2. It does not count the null values
  3. In following example we are returning the total record in salary table
  4. This function will treate salary table into one group and return one value from it
  5. Consider the following table salary:
  6. 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)

   

• SUM():

1. It return’s the total sum in a column
2. It return’s the sum of numeric column



3. Consider following salary table:



4. 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.

MAX():

1. It returns the maximum/largest value in a column



2. Consider following salary table:

• Example 1:In this example we have fired query to find the largest amount of salary column from salary table

Sql-query: select max(monthlysalary) as higestsalary from salary
Output: 32000

• 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,department from salary group by department order by higestsalary desc
Output:

MIN():

1. It will return the minimum value in a column



2. Consider the following salary table:

• Example : To get minimum salary from salary table.

Sql-query: select min(monthlysalary) as minimum from salary
Output: 2500

AVG():

1. It return’s the average value in a column



2. Consider the following salary table:

• Example 1: To get average salary disbursed from salary table

Sql-query: select avg(monthlysalary) as average from salary
Output: 13485

Thus, we learnt SQL aggregate functions in detail with examples.