Aggregate Function in MySQL

MySQL's aggregate functions, including AVG(), COUNT(), SUM(), MIN(), and MAX(), play a pivotal role in performing calculations on multiple values and presenting a consolidated result. Frequently utilized with SELECT statements, these functions are essential for data analysis. In SQL, aggregate functions process diverse data to produce a single value output. When combined with the GROUP BY clause and HAVING statement, these functions offer advanced data organization and filtering features in relational databases like MySQL, PostgreSQL, and others.

Syntax

The syntax for using an aggregate function in MySQL is as follows:

Syntax:

Parameters:

• The aggregate_function is the name of the function that you want to use.
• Examples of aggregate function in MySQL include COUNT, SUM, AVG, MIN, MAX, FIRST, LAST, or GROUP_CONCAT.
• The column_name is the name of the column to which you want to apply the function to.
• The table_name is the name of the table that you want to retrieve data from.
• The WHERE clause is optional and is used to specify a condition for selecting rows from the table.
• The GROUP BY clause is also optional and is used to group the result set by one or more columns.

Why do we Use Aggregate Functions?

Aggregate functions are used in MySQL to calculate a set of values and return a single value as a result. These functions are useful when we want to analyze data stored in a table and generate summary information, such as the total number of rows, the sum of values in a column, or the average of a set of values.

Using aggregate functions, we can filter data, calculate statistics, and improve performance(by limiting the data that needs to be processed).

Aggregate Functions in MySQL

COUNT() Function

The COUNT() function counts the number of rows in a table that meet a specified condition. This function can be used with or without the GROUP BY clause to calculate the number of rows in each group.

Syntax:

Let us consider the following customers table having the following sample data inside it. The customers table has five columns: id, name, city, email, and phone.

The following query can be used to create this table:

Query:

The next set of queries can be used to insert data into this customer's table.

Query:

There are a total of 10 rows of sample data present in this table. To view the data stored in this table, use the following query:

Query:

Customers table:

We will see the usage of the COUNT aggregation function in this table with the help of the following examples:

Example - 1: Finding the total number of rows in the customers table.

Code:

Output:

Explanation:

• This query returns the total number of rows in the customers table.
• The * is a shorthand for selecting all columns in the table. It is commonly used with the COUNT function to count the total number of rows in the table.
• In this query, FROM customers specifies the table from which to retrieve the data.
• By specifying only COUNT(*), the query returns a single value representing the number of rows in the customers table.
• Since there are ten rows in the customers table, the query will return 10 in this case.
• This type of query can help determine a table's size and track changes in the number of rows over time.

Example - 2: Return the total number of rows in the customers table where the city is Noida.

Code:

Output:

Explanation:

• This query returns the total number of rows in the customers table where the value in the city column equals Noida.
• The WHERE clause filters the results based on the condition: city = Noida, meaning only rows where the value in the city column equals Noida will be counted.
• By changing the value in the WHERE clause, you can count the number of customers in different cities.

SUM() Function

The SUM() function calculates the sum of values in a column. This function can be used with or without the GROUP BY clause.

Syntax:

Let us consider the following orders table with the following sample data. The orders table has three columns: customer_id, quantity, and order_date.

The following query can be used to create this table:

Query:

The next set of queries can be used to insert data into this orders table.

Query:

There are a total of 10 rows of sample data present in this table. To view the data stored in this table, use the following query:

Query:

Orders table:

Example - 1: Query to find the sum of values in the quantity column of the orders table.

Code:

Output:

Explanation:

• The output of the above query will be the sum of all values in the quantity column of the orders table.
• In the case of the above data, the output will be 28. This means the total quantity of items ordered by all customers in the orders table is 28.

Example - 2: This query will return the sum of values in the quantity column of the orders table where the customer_id is 10.

Code:

Output:

Explanation:

• The query will return the sum of the quantity values for all rows in the orders table where the customer_id equals 2.
• For the example orders table provided above, this query's output will be 7. This means that the customer with customer_id of 2 has ordered a total quantity of 7 items.

Now let us use the following products table, to understand the next set of aggregate functions in MySQL. The products table has three columns: product_name, category_id, and price.

Products table:

The product_name column contains the names of each product, the category_id column contains the ID of each product's category, and the price column contains the price of each product.

The following are the MySQL queries to create the above table and insert the above-said data into it.

Query to create Products table:

Query to insert data into the Products table:

Let us apply different aggregate functions to this products table.

AVG() Function

The AVG() function is used to calculate the average values in a column. This function can be used with or without the GROUP BY clause.

Syntax:

Example 1: This query will return the average value of the price column of the products table.

Code:

Output:

Example - 2: This query will return the average value of the price column of the products table where the category_id is 3.

Code:

Output:

MIN() Function

The MIN() function is used to retrieve the minimum value of a column. This function can be used with or without the GROUP BY clause.

Syntax:

Example - 1: This query will return the minimum value of the price column of the products table.

Code:

Output:

Example - 2: This query will return the minimum value of the price column of the products table where the category_id is 1.

Code:

Output:

MAX() Function

The MAX() function is used to retrieve the maximum value of a column. This function can be used with or without the GROUP BY clause.

Syntax:

Example - 1: This query will return the maximum value of the price column of the products table.

Code:

Output:

Example - 2: This query will return the maximum value of the price column of the products table where the category_id is 3.

Code:

Output:

FIRST() Function

The FIRST() function is used to retrieve the first value of a column in a group. This function can only be used with the GROUP BY clause.

Syntax:

In MySQL, FIRST() is not a built-in function. There is no direct equivalent to the FIRST() function in MySQL. However, you can achieve the same result using other MySQL functions like MIN() or MAX().

For example, if you want to find the first (i.e., minimum) price of each category in the products table, you can use the MIN() function with the GROUP BY clause as follows:

Code:

Output:

This will return a result set showing the minimum price of each category in the products table.

LAST() Function

The LAST() function retrieves the last value of a column in a group. This function can only be used with the GROUP BY clause.

Syntax:

In MySQL, LAST() is not a built-in function. There is no direct equivalent to the LAST() function in MySQL. However, you can achieve the same result using other MySQL functions like MIN() or MAX().

For example, if you want to find the last (i.e., maximum) price of each category in the products table, you can use the MAX() function with the GROUP BY clause as follows:

Code:

Output:

This will return a result set showing the maximum price of each category in the products table.

GROUP_CONCAT() Function

The GROUP_CONCAT() function concatenates values from a column into a single string. This function can be used with or without the GROUP BY clause.

Syntax:

Example - 1: This query will return a comma-separated list of all the product names in the products table.

Code:

Output:

Example - 2: This query will return a comma-separated list of all the product names for each category_id in the products table.

Code:

Output: