We often use the aggregate functions with the SELECT statement’s GROUP BY and HAVING clauses.
SQL aggregate functions
SQL aggregates are built-in functions used for various operations on multiple rows of a particular column, resulting in a single value.
An aggregate function allows you to perform the calculation on a set of values to return a single scalar value.
Aggregate Functions are:
- AVG()
- COUNT()
- MAX()
- MIN()
- SUM()
- STDDEV()
- VARIANCE()
Let’s understand all these functions with examples.
Avg in SQL
The avg() is a built-in SQL function that returns an average value after the calculation is performed in a numeric column.
Syntax
Select avg (column_name) from table_name;
Consider the following table.
Consider Table: (Customers)
| CUST_CODE | CUST_NAME | CITY | GRADE | AMOUNT |
| 101 | SHUBH | KOLKATA | 1 | 10000 |
| 201 | SOURAV | KOLKATA | 1 | 10000 |
| 301 | KISHAN | PATNA | 2 | 20000 |
| 401 | DILEEP | ALLAHABAD | 2 | 30000 |
| 501 | SHRUTI | PATNA | 3 | 40000 |
Query
select avg(amount) from customers;
Output
Explanation
The above query resulted in the average salary of customers in a table customer.
Count in SQL
The count() is a built-in SQL aggregate function used to count the total number of records in a table with or without a condition.
Syntax
Select count(column_name) from table_name;
Consider Table: (CUSTOMERS)
| CUST_CODE | CUST_NAME | CITY | GRADE | AMOUNT |
| 101 | SHUBH | KOLKATA | 1 | 10000 |
| 201 | SOURAV | KOLKATA | 1 | 10000 |
| 301 | KISHAN | PATNA | 2 | 20000 |
| 401 | DILEEP | ALLAHABAD | 2 | 30000 |
| 501 | SHRUTI | PATNA | 3 | 40000 |
Query: (Without a condition)
Select count(cust_code) from customers;
See the following output.
Explanation
The above query resulted in the total number of customers in a table.
Query: (With a condition)
Select count(cust_code) from customers where amount > 10000;
Output
Explanation
The above query returned the total number of customers whose amounts were more than 1000.
Max in SQL
The max() is a built-in SQL function that returns the maximum value in a column.
Syntax
Select max(column_name) from table_name;
Consider Table: (CUSTOMERS)
| CUST_CODE | CUST_NAME | CITY | GRADE | AMOUNT |
| 101 | SHUBH | KOLKATA | 1 | 10000 |
| 201 | SOURAV | KOLKATA | 1 | 10000 |
| 301 | KISHAN | PATNA | 2 | 20000 |
| 401 | DILEEP | ALLAHABAD | 2 | 30000 |
| 501 | SHRUTI | PATNA | 3 | 40000 |
Query
Select max(amount) from customers;
Output
Explanation
The max() function resulted in the max amount present in the customer table.
Min in SQL
The min() is a built-in SQL function that returns the minimum value in a column.
Syntax
Select MIN(column_name) from table_name;
Consider Table: (CUSTOMERS)
| CUST_CODE | CUST_NAME | CITY | GRADE | AMOUNT |
| 101 | SHUBH | KOLKATA | 1 | 10000 |
| 201 | SOURAV | KOLKATA | 1 | 10000 |
| 301 | KISHAN | PATNA | 2 | 20000 |
| 401 | DILEEP | ALLAHABAD | 2 | 30000 |
| 501 | SHRUTI | PATNA | 3 | 40000 |
Query
Select min(amount) from customers;
Output
Explanation
The min() function query resulted in the min amount present in the customer table.
Sum in SQL
The sum() is a built-in SQL function used to return the submission of all numeric values in a column.
Syntax
Select SUM(column_name) from table_name;
Consider Table: (CUSTOMERS)
| CUST_CODE | CUST_NAME | CITY | GRADE | AMOUNT |
| 101 | SHUBH | KOLKATA | 1 | 10000 |
| 201 | SOURAV | KOLKATA | 1 | 10000 |
| 301 | KISHAN | PATNA | 2 | 20000 |
| 401 | DILEEP | ALLAHABAD | 2 | 30000 |
| 501 | SHRUTI | PATNA | 3 | 40000 |
Query
Select sum(amount) from customers;
Output
Explanation
The sum() function query resulted in the total sum of the amount column.
STDDEV in SQL
The stddev() is a built-in SQL function that returns the standard deviation of a selected column.
Syntax
Select STDDEV(column_name) from table_name;
Consider Table: (CUSTOMERS)
| CUST_CODE | CUST_NAME | CITY | GRADE | AMOUNT |
| 101 | SHUBH | KOLKATA | 1 | 10000 |
| 201 | SOURAV | KOLKATA | 1 | 10000 |
| 301 | KISHAN | PATNA | 2 | 20000 |
| 401 | DILEEP | ALLAHABAD | 2 | 30000 |
| 501 | SHRUTI | PATNA | 3 | 40000 |
Query
Select STDDEV(amount) from customers;
Output
Explanation
The STDDEV query resulted in the standard deviation of an amount column.
Variance in SQL
The variance() is a built-in SQL function that returns the Variance of a selected column.
Syntax
Select VARIANCE(column_name) from table_name;
Consider Table: (CUSTOMERS)
| CUST_CODE | CUST_NAME | CITY | GRADE | AMOUNT |
| 101 | SHUBH | KOLKATA | 1 | 10000 |
| 201 | SOURAV | KOLKATA | 1 | 10000 |
| 301 | KISHAN | PATNA | 2 | 20000 |
| 401 | DILEEP | ALLAHABAD | 2 | 30000 |
| 501 | SHRUTI | PATNA | 3 | 40000 |
Query
Select variance(amount) from customers;
Output
Explanation
The above query resulted in the variance of the amount column.
That’s it for this tutorial.
