SQL Aggregate Functions basically operate on multiple columns to perform the operations and serve to represent the output as an entity representing the operation executed.
Syntax:
|
1 |
aggregate_function (DISTINCT | ALL expression) |
- DISTINCT enables the user to select distinct values from the table i.e. if multiple attributes contain the same value, then only single distinct value is considered for the calculations.
- ALL makes sure that even the repeated values are considered for the calculations.
Aggregate Functions in SQL:
- AVG()
- MIN()
- MAX()
- SUM()
- COUNT()
1. SQL AVG() function
SQL AVG() function returns the average of all the selected values from the corresponding column of the table.
Let’s us consider the following table to understand the Aggregate functions:
Table Name: Details
| ID | Name | Amount | Age |
|---|---|---|---|
| 1 | Safa | 5000 | 21 |
| 2 | Aman | 2500 | 23 |
| 3 | Rehaan | 20000 | 25 |
| 4 | Seema | 12000 | 25 |
Example:
|
1 |
select AVG(Amount) from Details; |
Output:
|
1 |
9875 |
2. SQL MIN() function
SQL MIN() function returns the minimum value of all the selected values from the corresponding column of the table.
Example:
|
1 |
select MIN(Amount) from Details; |
Output:
|
1 |
2500 |
3. SQL MAX() function
SQL MAX() function returns the maximum value of all the selected values from the corresponding column of the table.
|
1 |
select MAX(Amount) from Details; |
Output:
|
1 |
20000 |
4. SQL SUM() function
SQL SUM() function returns the summation of all the selected values from the corresponding column of the table.
Example 1: Basic Understanding of SUM() Function
|
1 |
select SUM(Amount) from Details; |
Output:
|
1 |
39500 |
Example 2: SQL SUM() with GROUP BY Clause
SQL SUM() can be framed together with SQL GROUP BY Clause to represent the output results by a particular label/values.
|
1 2 3 4 |
SELECT SUM(Amount),Name FROM Details WHERE Age>21 GROUP BY Name; |
Output:
Example 3: SQL SUM() with HAVING Clause
SQL SUM() function can be used along with SQL HAVING Clause; HAVING Clause is basically used to specify the condition to be operated on the set of values in the table.
|
1 2 3 4 |
SELECT SUM(Amount),Name FROM Details GROUP BY Name HAVING SUM(Amount)>2500; |
Output:
5. SQL COUNT() function
Example 1: COUNT() function to return the count of a particular column of a table
|
1 |
select COUNT(Amount) from Details; |
Output:
|
1 |
4 |
Example 2: COUNT(*) function
This function returns the count of all the values present in the set of records of a table.
|
1 |
SELECT count(*) from Details; |
Output:
|
1 |
4 |
Example 3: COUNT() with WHERE Clause
|
1 2 |
SELECT count(*) from Details where Age<25; |
‘Output:
|
1 |
2 |
Example 4: COUNT() with GROUP BY Clause
|
1 2 3 |
SELECT count(Amount),Name from Details where Age<25 Group by Name; |
Output:
Example 5: COUNT() with HAVING Clause
|
1 2 3 |
SELECT count(Amount),Age, Name from Details Group by Name HAVING Age>=25; |
Output:
Conclusion
Thus, in this article, we have understood different SQL Aggregate Functions.