COUNT(), SUM(), AVG(), MIN(), MAX()
Aggregate functions in SQL database tables are used to calculate table column values and preview the result in the console window screen. Popular common aggregate functions in SQL include COUNT(), SUM(), AVG(), MIN(), and MAX() functions by default. You can use these aggregate functions in existing SQL database tables to group SQL table rows based on specific condition criteria and display multiple table function values.
SQL COUNT() function.
The COUNT() function in SQL tables is used to count the number of all rows in the result of a particular table. You can customize the COUNT function as per your requirement, or count both unique and all rows of a table.
Syntax of SQL COUNT function.
SELECT COUNT(column_name) FROM table_name WHERE condition;
Remember, if you pass a table column name to the COUNT() function, it counts the non-NULL values in that column.
If you use the COUNT(*) function in a SQL table, it counts all the rows including the rows with NULL values.
COUNT Function Example.
To count the total number of employees in the employee table.
SELECT COUNT(*) FROM employe;
This SQL command displays the total number of rows (employees) in the existing table.
If you want to count the number of employees in your employee table HR department.
SELECT COUNT(*) FROM employe WHERE department = ‘HR’;
SUM() Function in SQL.
SUM() function in SQL table is used to calculate the sum of the total values of numeric column of a particular table.
SUM function syntax in SQL.
SELECT SUM(column_name) FROM table_name WHERE condition;
It returns the sum of all non-zero values in a particular column in the current SQL table.
Example of Sum function in SQL table.
To calculate the total salary of all employees in employee table.
SELECT SUM(salary) FROM employe;
If you want to calculate the total salary of employees in IT department only in your employee table.
SELECT SUM(salary) FROM employe WHERE department = ‘IT’;
AVG() function in SQL.
AVG() function in SQL table is used to calculate the average value of numeric column of a table.
AVG() syntax in SQL table.
SELECT AVG(column_name) FROM table_name WHERE condition;
Remember that avg() function displays the average value of all non-NULL values in a particular column.
Example of AVG() function.
To calculate the average salary of all employees in a SQL table.
SELECT AVG(salary) FROM employe;
If you want to calculate the average salary of only HR department employees in the employee table.
SELECT AVG(salary) FROM employe WHERE department = ‘HR’;
MIN() function in sql.
In SQL table, MIN() function is used to find the minimum value in a column.
MIN() syntax in SQL.
SELECT MIN(column_name) FROM table_name WHERE condition;
This function returns the smallest minimum numeric value of the specified column in the existing table.
Example of MIN() in SQL table.
To find the minimum salary of all employees in a SQL table.
SELECT MIN(salary) FROM employe;
If you want to find the minimum salary of an employee in the finance department in your existing SQL table.
SELECT MIN(salary) FROM employe WHERE department = ‘Finance’;
MAX() function in sql.
MAX() function in SQL table is used to find the maximum value in a column of a table.
MAX() syntax in SQL table.
SELECT MAX(column_name) FROM table_name WHERE condition;
It returns the largest maximum value of a column in a particular table.
Example of MAX() in SQL table.
To find the maximum salary value among all employees in the SQL table.
SELECT MAX(salary) FROM employe;
If you want to find the maximum salary of employees in IT department only in your employee table.
SELECT MAX(salary) FROM employe WHERE department = ‘IT’;
Summary of Aggregate Functions
| Function | Description | Example Query |
| COUNT() | Count function used to Returns the number of rows (or non-NULL values) in active table column. | SELECT COUNT(*) FROM employe WHERE department = ‘HR’; |
| SUM() | Sum function used to Returns the total sum of a numeric column in active table | SELECT SUM(salary) FROM employe; |
| AVG() | Avg function used to Returns the average value of a numeric column in active table | SELECT AVG(salary) FROM employe WHERE department = ‘IT’; |
| MIN() | Min function used to Returns the smallest value in a column in active table | SELECT MIN(salary) FROM employe; |
| MAX() | Max function used to Returns the largest value in a column in active table | SELECT MAX(salary) FROM employe; |
Extract aggregate function from SQL table.
- COUNT() – Counts the number of null or non-null values in a column in the existing SQL table.
- SUM() – Calculates the sum of the total values of a numeric column in the existing SQL table.
- AVG() – Displays the average value of a numeric column in the existing SQL table.
- MIN() – Finds the minimum value in a column in the existing SQL table.
- MAX() – Finds the maximum value in a column in the existing SQL table.
