SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial

SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial

SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial is today’s topic. SQL provides many 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

SQL Aggregate Functions Example

#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.

Count() in SQL

#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

count_where

#Explanation

The above query returned a 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.

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

Max() in SQL

#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

Min() in SQL

#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 value 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

Sum() in SQL

#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

STDDEV() in SQL

#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

Variance() in SQL

#Explanation

The above query resulted in the variance of the amount column.

Finally, SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial is over.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *