How do you use aggregate functions like COUNT, SUM, AVG, MAX, and MIN in MySQL? — MySQL Interview Question #14

In MySQL, aggregate functions are used to perform calculations on a set of values and return a single value. These functions are commonly used in SQL queries to summarize data and perform statistical calculations on groups of records.

The syntax for using aggregate functions in MySQL is as follows:

SELECT aggregate_function(column_name)
FROM table_name
WHERE condition;

Here, ‘aggregate_function‘ can be one of the following:

- ‘COUNT‘: Returns the number of rows that matches the condition.

- ‘SUM‘: Returns the sum of the values in the column that matches the condition.

- ‘AVG‘: Returns the average of the values in the column that matches the condition.

- ‘MAX‘: Returns the maximum value in the column that matches the condition.

- ‘MIN‘: Returns the minimum value in the column that matches the condition.

Let’s consider an example where we have a table called "orders" with the following columns: "order_id", "customer_id", "order_date", and "amount".

To count the number of orders in the table, we can use the ‘COUNT‘ function as follows:

SELECT COUNT(order_id)
FROM orders;

This query will return the total number of orders in the "orders" table.

To calculate the average order amount, we can use the ‘AVG‘ function as follows:

SELECT AVG(amount)
FROM orders;

This query will return the average value of the "amount" column in the "orders" table.

To find the maximum order amount, we can use the ‘MAX‘ function as follows:

SELECT MAX(amount)
FROM orders;

This query will return the maximum value of the "amount" column in the "orders" table.

Similarly, to find the minimum order amount, we can use the ‘MIN‘ function as follows:

SELECT MIN(amount)
FROM orders;

This query will return the minimum value of the "amount" column in the "orders" table.

Aggregate functions can also be used with the ‘GROUP BY‘ clause to perform calculations on individual groups of records. For example, to calculate the total amount for each customer, we can use the ‘SUM‘ function with the ‘GROUP BY‘ clause as follows:

SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

This query will return the total sum of order amounts for each customer in the "orders" table.