SQL Aggregate Functions

In this article let us explore a new topic in SQL called aggregate functions. First let us understand what is aggregate function and how does it actually work.

What is aggregate function?

Aggregate functions in SQL are unique functions that work on a group of rows in a table and produce a single value as a result. These operations are used to calculate a set of numbers and produce summary statistics like sum, count, average, maximum, and minimum. SQL queries frequently employ aggregate procedures to condense data from one or more tables.

After grouping and aggregating, aggregate functions can also be used with the HAVING or WHERE clause to further filter the data. A condition involving an aggregate function can be used to filter the results of a query using the HAVING or WHERE clause.

Below are a few frequently used aggregate functions in SQL. Let us understand each of these functions with the help of various examples.

1. Count()
2. Sum()
3. Avg()
4. Min()
5. Max()

1. COUNT():

This function returns the number of records(rows) in a table. The Syntax of the count() function is mentioned below.

Syntax:

Here column_name is the name of the column that you want to know about the number of values and the WHERE clause is used only when specific data which satisfies a condition is extracted.

Ex: consider below "Student" table for understanding the functioning of the Count() function.

Student

Output:

Now, let us attach the WHERE clause to the COUNT() and see how it works.

select COUNT(*) FROM Student WHERE Department = 'CSE';

Output:

2. SUM():

This function returns the sum of all values of a column in a table. Here is the syntax for the sum() function.

Syntax:

Here column_name is the name of the column for which you wanted to find the sum, of values and the WHERE clause is used only when you wanted to apply the SUM() function to the data which satisfies the condition.

Ex: observe the below example for understanding the functioning of the SUM function.

Table: sales

Output:

Now let us calculate the sum of the prices of mobiles which are present in the sales table.

Output:

3. AVG()

This function will return the average of all values present in a column. The syntax of the AVG() function is given below.

Syntax:

Here column_name is the name of the column for which you need to find the average of the values present in the column and the WHERE clause is used only when you wanted to apply AVG() function to the data which satisfies the condition.

Let us consider again the "sales" table for applying the AVG() on it and let us see how it works.

Table: Sales

Now let us find out the average of all prices of the products using the AVG() function.

Output:

Now let us find the average price of only mobiles by using the WHERE clause in combination with AVG() function.

Output:

4. MIN():

This function produces the lowest value in a column for a group of rows that satisfy a given criterion. The Syntax of the MIN() function is as follows

Syntax:

Let us consider the below employees table to illustrate the functioning of the MIN function.

employees

Now let us apply the MIN function to find out the minimum value in the salary column.

output:

Now let us find us find out the minimum salary from the R&D department.

Output:

5. MAX()

The MAX function in SQL is used to return the highest value in a column for a group of rows that satisfy a given condition in a table. The MAX syntax is as follows:

Syntax:

Consider again the employees table to understand the functioning of MAX function.

employees

Now let us find us find out the maximum value in the salary column using MAX() function.

Output:

Now find out the highest salary of the marketing department by using the WHERE clause in combination with MAX() function.

Output:

This is all about Aggregate functions in SQL. You may summarise and analyze data from a table or set of tables using aggregate functions. They are necessary for producing summary statistics and can assist you in finding simple answers to your complex data questions.