Getting started with MySQL-9: grouping data

Outline

1. Create a group

2. Filter group

3. SELECT clause order

Grouping allows you to divide the data into logical groups so that each grouping can be aggregated.

1. Create a group

Mysql> SELECT vend_id, COUNT (*) as num_prods, from products, GROUP by vend_id;+---------+-----------+| vend_id |    Num_prods |+---------+-----------+|         1001 |    3 | |         1002 |    2 | |         1003 |    7 | |         1005 | 2 |+---------+-----------+

GROUP by clause instructs MySQL to group data, and then aggregates each grouping instead of the entire result set .

Before you can use the GROUP BY clause, you need to know some important provisions.

• group by clause can contain any number of columns. This allows grouping to be nested, providing finer control over the grouping of data.

• group Each column listed in the BY clause must be a retrieval column or a valid expression (but not a clustered function). cannot be used. Because group by takes precedence over select execution.

When you build a query, all the nonclustered columns in the projection list of the Projection clause must also be included in the GROUP by clause. SELECT statements that have a GROUP BY clause must return a row for each group. Columns that are listed after group by can only reflect a specific value in a set, and can return that value. However, columns that are not listed after group by can contain different values in the rows that are contained in the group.

2. Filter group having

In addition to using group by grouping data, MySQL also allows filtering of groupings, including which groupings to exclude and which groups are excluded. filtering is based on a complete grouping instead of individual rows .

When it comes to filtering, you definitely think of the WHERE clause. However, where filtering specifies "rows" instead of "grouping". In fact, where there is no concept of grouping.

This provides another clause for MySQL, having. having very similar to where. The only difference is where the "row" is filtered, and the having filter "group". All techniques and options for where are applicable to having, their syntax is the same, but the keyword is different.

Having support for all where operators.

Having and WHERE differences:

where the data is filtered before grouping, having to filter the data after grouping. This is an important difference, where excluded rows are not included in the grouping. This may change the calculated value, thereby affecting the grouping that is filtered out of the HAVING clause based on these values. Where is the filter row, and having must be based on a complete grouping filter. (All other uses are the same)

3. SELECT clause order

Selectfromwhere--row level filtering group Byhaving--group-level filtering order Bylimit

This article is from the "Share Your Knowledge" blog, so be sure to keep this source http://skypegnu1.blog.51cto.com/8991766/1435512