mysql data grouping: filter grouping

mysql filter grouping

In addition to grouping data using group by, MySQL also allows filtering groups, stipulating which groups are included and which groups are excluded. For example, you might want to list all customers who have at least two orders. To derive this data, filtering must be done based on complete groups rather than individuals.

We have already seen the where clause in action. However, where does not accomplish the task in this example because the where filter specifies rows rather than groups. In fact, where has no concept of grouping.

So, what to use instead of where? MySQL provides another clause for this purpose, which is the having clause. having is very similar to where. In fact, all types of where clauses you have learned so far can be replaced by having. The only difference is where filters rows, while having filters groups.

Note: having supports all where operators.

So, how to filter groups? Please look at the example below:

Input:

select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2;

Output:

Analysis: The first 3 lines of this select statement are similar to the above statement. The last line adds a having clause, which filters those groups with count(*>)>=2 (more than two orders).

As you can see, the where clause does not work here because the filtering is based on the grouped aggregate value and not the specific row value.

The difference between having and where: Here is another way to understand it, where filters before data grouping, and having filters after data grouping. This is an important distinction, rows excluded by where are not included in the grouping. This may change the calculated values, thereby affecting the groupings in the having clause that are filtered out based on these values.

So, is there any need to use both where and having clauses in one statement? In fact, there is. Suppose you want to further filter the above statement so that it returns more than two customers in the past 12 months. To achieve this, you can add a where clause to filter out orders placed within 12 months. Then add a having clause to filter out groups with more than two orders.

For better understanding, please see the example below, which lists suppliers with products above 2 (inclusive) and price 10 (inclusive) or above:

Input:

select vend_id,count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >=2;

Output:

Analysis: In this statement, the first line is a basic SELECT using an aggregate function, which is very similar to the previous example. The WHERE clause filters all rows where prod_price is at least 10. The data is then grouped by vend_id and the HAVING clause filters groupings with a count of 2 or above.

If there is no WHERE clause, two more rows will be retrieved (supplier 1002, all products sold by

prices are below 10; Supplier 1001, selling 3 products, but only one product has a higher price equal to 10):

Input: select vend_id,count(*) as num_prods from products group by vend_id having count(*) >=2;

Output:

【Related recommendations】

1. mysql data grouping and sorting and SELECT clause order

2. mysql data grouping: create grouping
   

The above is the detailed content of mysql data grouping: filter grouping. For more information, please follow other related articles on the PHP Chinese website!