Applying Aggregate Functions and GROUP BY in MySQL

The aggregation function is used to perform calculations on a set of values ​​and return a single value. Common ones include COUNT, SUM, AVG, MAX, and MIN; GROUP BY groups data by one or more columns and applies an aggregation function to each group. For example, GROUP BY user_id is required to count the total order amount of each user; SELECT user_id, SUM(amount) FROM orders GROUP BY user_id; non-aggregated fields must appear in GROUP BY; multiple fields can be used for multi-condition grouping; HAVING is used instead of WHERE after grouping; application scenarios such as counting the number of classified products, maximum order users, monthly sales trends, etc. Mastering these can effectively solve data statistics problems.

In MySQL query, when we need to count and summarize the data, aggregate functions (such as COUNT , SUM , AVG , MAX , MIN ) come in handy. But having an aggregation function alone is not enough. Many times we need to group it by a certain field before doing statistics. At this time, we must use GROUP BY in conjunction with it.

What is an aggregate function?

An aggregate function is a function that performs calculations on a set of values ​​and returns a single value. Common ones include:

• COUNT() : count the number of rows
• SUM() : Sum
• AVG() : Find the average value
• MAX() and MIN() : Find the maximum or minimum value

For example, if you want to know the total amount of all orders in a certain order table, you can use:

 SELECT SUM(amount) FROM orders;

This adds up amount fields of the entire table and outputs a total number. But what if you want to know the total order amount for each user? GROUP BY is needed at this time.

What does GROUP BY do?

The function of GROUP BY is to group data by one or more columns, and then apply an aggregate function to each group separately.

For example, suppose you have a user order table orders with user_id and amount fields. If you want to know the total order amount of each user, you can write it like this:

 SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

The meaning of this statement is: grouped by user_id , each group of data is performed once SUM(amount) operation, and finally the total amount of each user is returned.

It should be noted that non-aggregated fields that appear in SELECT generally need to appear after GROUP BY . Otherwise, errors may occur or results may be unpredictable, especially if SQL mode ONLY_FULL_GROUP_BY is enabled.

Common misunderstandings and precautions

Don't miss GROUP BY

Many beginners make this mistake:

 SELECT user_id, SUM(amount)
FROM orders;

This statement will report an error in some database systems because user_id is not aggregated and does not appear in GROUP BY clause.

GROUP BY Multiple Fields

If you want to count by user and order year, you can write this:

 SELECT user_id, YEAR(order_date), SUM(amount)
FROM orders
GROUP BY user_id, YEAR(order_date);

HAVING is used after aggregation

If you want to filter out users whose "total amount is greater than 1000", you cannot use WHERE , but HAVING :

 SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING total > 1000;

WHERE filters rows before grouping, while HAVING filters the results after grouping.

Examples of practical application scenarios

Scenario 1: Statistics the quantity of products under each category

 SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id;

Scenario 2: Find the top 5 users with the most orders

 SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
ORDER BY order_count DESC
LIMIT 5;

Scenario 3: Check the sales trends of each month

 SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS total_sales
FROM orders
GROUP BY month
ORDER BY month;

Basically that's it. Mastering the combination of aggregate functions and GROUP BY can help you solve most data statistics problems. Although the syntax is not complicated, it is easy to ignore some details in actual queries, such as field omissions, misuse of WHERE , etc., and you can become proficient after practicing a few more times.

The above is the detailed content of Applying Aggregate Functions and GROUP BY in MySQL. For more information, please follow other related articles on the PHP Chinese website!