SQL Aggregate Functions – Example, Definition, Syntax

In this tutorial, we are going to discuss SQL aggregate functions like COUNT(), SUM(), MAX(), MIN(). AVG() with an example, definition & syntax. you will learn it in some simple steps that are very simple to understand.

Aggregate Functions in SQL

Aggregate functions calculate the string & numeric values of multiple records of a particular column and return a numeric result.

Using aggregate functions in SQL, you can find out the largest value, lowest value, average value, total number of records, & total sum of values of a specified column.

Aggregate functions mainly used with the GROUP BY  & HAVING clause in a SELECT statement.

Syntax –

aggregate_function(column_name)

There are five aggregate functions that are often used in SQL

• COUNT() – It is used to find out total records of a column
• SUM() – It is used to find out the total sum of values of a column
• MAX() – It is used to find out the largest values of a column
• MIN() –  It is used to find out the  lowest values of a column
• AVG() –  It is used to find out the  average values of a column

Example –

Now, we will understand the concept of aggregate functions with the following table from the next steps.

Use the following query to create a table

CREATE TABLE students
 (
  student_name varchar(50),
  gender varchar(20),
  city  varchar(100),
  salary int(10)
 );

Also, use the following query to insert some records into the created table.

INSERT INTO employees( student_name, gender, city, salary) VALUES 
( 'Amit Kumar', 'Male', 'Jaipur', 80000),
('Sumit Kumar', 'Male', 'Noida', 4200), 
( 'Monika Singh', 'Female', 'Chennai', 30000), 
('Aaliya Khan', 'Female', 'Chennai', 40000),
( 'Sumit Kumar', 'Male', 'Patna', 35000),
('Monika Singh', 'Female', 'New Delhi', 60000),
('Rapson Jani', 'Male', 'Patna', 70000), 
('Rapson Jani', 'Male', 'New Delhi', 25000), 
('Sumit Kumar', 'Male', 'New Delhi', 85000),
('Monika Singh', 'Female', 'Patna', 32000);

Table Name – students

 

SQL COUNT()

Definition –

The COUNT() returns the total number of records of a column that has a string or numeric values.

The COUNT() accepts only a single column name as a parameter.

If you need to find out the total records of a column or table, you can use the COUNT() function.

Syntax –

COUNT(column_name)

Example –

Use the following query to find out the total records of students table

Query –

SELECT COUNT (*) FROM students;

Output –

COUNT(*)

10

SQL SUM()

Definition –

The SUM() returns the total sum of records of a column that has only numeric values.

The SUM() also accepts only a single column name as a parameter.

If you need to find out the total sum of column values, you can use the SUM() function.

Syntax –

SUM(column_name)

Example –

Use the following query to find out the total sum of students’ salary

Query –

SELECT SUM(salary) FROM students;

Output –

SUM(salary)

461200

SQL MAX()

Definition –

The MAX() returns the largest record of a column that has only numeric values.

The MAX() also accepts only a single column name as a parameter.

If you need to find out the largest value of a column, you can use the MAX() function.

Syntax –

MAX(column_name)

Example –

Use the following query to find out the largest salary of a student.

Query –

SELECT MAX(salary) FROM students;

Output –

MAX(salary)

85000

SQL MIN()

Definition –

The MIN() returns the lowest record of a column that has only numeric values.

The MIN() also accepts only a single column name as a parameter.

If you need to find out the lowest value of a column, you can use the MIN() function.

Syntax –

MIN(column_name)

Example –

Use the following query to find out the lowest salary of a student

Query –

SELECT MIN(salary) FROM students;

Output –

MIN(salary)

42000

SQL AVG()

Definition –

The AVG() returns the average value of a column that has only numeric values.

The AVG() also accepts only a single column name as a parameter.

If you need to find out the average value of a column, you can use the AVG() function.

Syntax –

AVG(column_name)

Example –

Use the following query to find out the average salary of a student

Query –

SELECT AVG(salary) FROM students;

Output –

 

Tutorial Summary

Dear Developer, I hope that You have learned  Aggregate Function in SQL with Syntax & Example. Now you can easily execute it

If you have any questions regarding this tutorial, Kindly ask me through the comment box. Even you can suggest other web development topics to learn in detail.