SQL Aggregate Functions | Min, Max, Count, Avg, Sum In SQL
SQL aggregate functions are inbuilt functions that are used for performing various operations in data. Aggregate Functions are used for performing operations on multiple rows of a particular column and result in a single value. An aggregate function allows you to perform the calculation on a set of values to return the single scalar value.
We often use the aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.
Aggregate Functions are:
- AVG()
- COUNT()
- MAX()
- MIN()
- SUM()
- STDDEV()
- VARIANCE()
Let’s understand all this function with examples.
#Avg() in SQL
The avg() function is used to return an average value after the calculation 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() function is used to count a total number of records in a table with a condition 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 amount where more than 1000.
#Max() in SQL
The max() function is used to return 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() function is used to return 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() function is 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() function is used to return 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() function is used to return 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.
Finally, SQL Aggregate Functions (Min, Max, Count, Avg, Sum) Tutorial is over.