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():

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:

Explanation: On execution of above query, the result is shown above. In this count(*) function counts the total number of rows in a table.

Using Column Alias:

output:

Explanation: On execution of above query, the result is shown above. In this count function calculates the total number of rows in the table and stores the result in alias as Total_Count.

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

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

Output:

Explanation: On execution of above query, the result is shown above. In this count(*) function counts the number of rows in a table where the department name is equal to CSE.

COUNT with distinct clause:

This function returns the number of unique records (rows) in a table.

Syntax:

Example:

Select COUNT (distinct department) FROM Student;

Output:

Explanation: On execution of above query, the result is shown above. In this count(*) function counts the distinct values of the department column in a table.

Using Group by clause with COUNT():

Output:

Explanation: On execution of above query, the result is shown above. In this count function calculates the total number of rows in the table group by department and after calculating the stores the result is alias as Total_Count.

Using Having clause with COUNT():

Output:

Explanation: On execution of above query, the result is shown above. In this count function calculates the number of rows in the table group by department where department count is greater than 2 and after calculating the stores the result is alias as Total_Count.

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:

Explanation: On execution of above query, the result is shown above. In this sum function calculates the sum of price in a table.

Using Column Alias:

output:

Explanation: On execution of above query, the result is shown above. In this sum function calculates the total price of a table and stores the result in alias as Total_Sum.

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

Output:

Explanation: On execution of above query, the result is shown above. The sum function calculates the sum of price in a table where product_name is equal to mobile.

Using Group by clause with SUM():

Output:

Explanation: On execution of above query, the result is shown above. This sum function calculates the total sum of price by grouping products in the table and stores the result in alias as Total_Sum.

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:

Explanation: On execution of above query, the result is shown above. In this avg function calculates the average price of a table.

Using Column Alias:

output:

Explanation: On execution of above query, the result is shown above. In this avg function calculates the average price of a table and stores the result in alias as Average_Salary.

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

Output:

Explanation: On execution of above query, the result is shown above. In this avg function calculates the average price of the table where product name is equal to mobile.

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:

Explanation: On execution of above query, the result is shown above. In this min function calculates the minimum salary of a table.

Using Column Alias:

output:

Explanation:On execution of above query, the result is shown above. In this min function calculates the minimum salary of a table and stores the result in aliases such as Lowest_Salary.

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

Output:

Explanation: On execution of above query, the result is shown above. In this min function calculates the minimum salary of a table where the department name is equal to R&D.

Using Group by clause with MIN():

Output:

Explanation: On execution of above query, the result is shown above. In this min function calculates the minimum salary by grouping the department in the table and stores the result in alias as Lowest_Salary.

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:

Explanation: On execution of above query, the result is shown above. In this max function calculates the maximum salary of a table.

Using Column Alias:

output:

Explanation: On execution of above query, the result is shown above. In this max function calculates the maximum salary of a table and stores the result in aliases such as Higest_Salary.

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

Output:

Explanation: On execution of above query, the result is shown above. In this max function calculates the maximum salary of a table where the department name is equal to marketing.

Using Group by clause with MAX():

Output:

Explanation: On execution of above query, the result is shown above. In this count function calculates the total number of rows in the table and stores the result in alias as Total_Count.

6. VARIANCE(n)

The VARIANCE function gives the variance of all values for a group of rows except NULL values. Here n is the column name of NUMBER data type.

Syntax:

Example:

Output:

Explanation: On execution of above query, the result is shown above. In this variance function calculates the variance of the salary in a table.

7. STDDEV(n)

The STDDEV function returns the standard deviation of all values for a group of rows except NULL values. SQL calculates the standard deviation as the square root of variance. Here n is the column name of NUMBER data type.

Syntax:

Example 1:

Output:

Explanation: On execution of above query, the result is shown above. In this stddev function calculates the standard deviation of the salary in a table.

Example 2:

Output:

Explanation: On execution of above query, the result is shown above. In this we can calculate the standard deviation by firstly calculating the sqrt after that variance of salary in a table.

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.