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:

1. AVG()
2. COUNT()
3. MAX()
4. MIN()
5. SUM()
6. STDDEV()
7. 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.