The relationship between group by and having in mysql

GROUP BY groups data of the same value and executes the aggregation function; HAVING filters the grouped data: set conditions based on the aggregation function or grouping key; after GROUP BY grouping, HAVING filters the grouping results and is only applicable to aggregation the subsequent data.

The relationship between GROUP BY and HAVING in MySQL

GROUP BY and HAVING are both used in MySQL for aggregation and Clause to filter data. The two work together to summarize and filter the grouped data.

GROUP BY

The GROUP BY clause groups data of the same value together and performs aggregate functions such as SUM, COUNT, and AVG on each group. It divides the data set into different groups, each group has a unique key value or set of key values.

HAVING

The HAVING clause is used to filter data grouped by GROUP BY. It is similar to the WHERE clause, but only applies to aggregated data. The HAVING clause allows users to filter groups based on aggregation results, for example:

• Filter groups with a specific average value
• Filter groups that contain a number of groups greater than a specific threshold
• Filter groups with null or non-null values

Relationship

The relationship between GROUP BY and HAVING is as follows:

• GROUP BY first groups the data, and then HAVING filters the grouped data.
• Conditions in the HAVING clause must use an aggregate function or reference a grouping key.
• HAVING Before filtering the group, you must first perform GROUP BY grouping.

Example

The following query uses GROUP BY and HAVING to group and filter the orders table:

<code class="sql">SELECT product_category, SUM(quantity) AS total_quantity
FROM orders
GROUP BY product_category
HAVING SUM(quantity) > 100;</code>

This query sorts orders by product Categories are grouped and the total quantity for each category is calculated. It then uses HAVING to filter out categories whose total number is less than 100.

The above is the detailed content of The relationship between group by and having in mysql. For more information, please follow other related articles on the PHP Chinese website!