MYSQL requires reading notes 12th and Chapter 13 grouping data-mysql tutorial

1. groupby can contain any number of columns 2. in groupby, each column must be a search column or a valid expression (but not a clustering function). 3. except for the aggregate function, each column in the select statement must appear in the group by clause. if the group column is Null, Null is returned as a group. the groupby clause must appear in

1. group by can contain any number of columns 2. in group by, each column must be a search column or a valid expression (but not a clustering function. except for the aggregate function, each column in the select statement must appear in the group by clause. if the group column is Null, Null is returned as a group. the group by clause must appear in

1. group by can contain any number of columns 2. in group by, each column must be a search column or a valid expression (but not a clustering function. except for the aggregate function, each column in the select statement must appear in the group by clause. if the group column has a Null value, Null is returned as a group. the group by clause must appear after the where clause. The difference between where and have before order by is: where is used before grouping, and having is used to filter SELECT vend_id and COUNT (*) After grouping (*) AS num_prods FROM products group by vend_id;The group by clause instructs MySQL to GROUP data and then aggregates each GROUP rather than the entire result set. Before using the group by clause, you need to know some important rules: 1. the group by clause can contain any number of columns. This allows grouping to be nested and provides more detailed control for data groups. 2. if a GROUP is nested in the group by clause, the data will be summarized on the last specified GROUP. In other words, when a group is created, all specified columns are calculated together (so data cannot be retrieved from individual columns. each column listed in the group by clause must be a search column or a valid expression (but not a clustering function ). If an expression is used in SELECT, the same expression must be specified in the group by clause. Aliases cannot be used. 4. except for the aggregate calculation statement, each column in the SELECT statement must be given in the group by clause. 5. if the GROUP column contains a NULL value, NULL is returned as a GROUP. 6. the group by clause must appear after the where clause. Before the order by clause. Note: If you use rollup with the with rollup keyword, you can obtain the values of each group and each group at the aggregation level (for each group. SELECT vend_id, COUNT (*) AS num_prods FROM products groups by vend_id with rollup; 2: We have seen the role of the WHERE clause in filtering GROUPS, but in this example, the WHERE clause cannot complete the task. Because WHERE filters the specified rows rather than groups. In fact, the WHERE clause does not have the concept of grouping. MYSQL provides similar statements for this purpose, that is, the HAVING clause. HAVING is very similar to WHERE. In fact, HAVING can be used to replace all types of WHERE clauses that have been learned so far. The only difference is that the WHERE clause filters rows, while the HAVING clause filters rows. SELECT cust_id, COUNT (*) AS orders FROM orders group by cust_id having count (*)> = 2; note: the difference between HAVING and WHERE is filtered before the data GROUP, HAVING filters data groups. This is an important difference. the rows excluded by the WHERE clause do not appear in the group. The group value may be changed, which affects the HAVING clause to filter out groups based on these values. For better understanding, see the following example. it lists suppliers with more than two products with a price of more than 10. SELECT vend_id, COUNT (*) AS num_prods FROM products where prod_price> = 10 group by vend_id having count (*)> = 2; note that when using the group by clause, the order by clause should also be provided. This is the only way to ensure correct data sorting. SELECT order_num, SUM (quantity * item_price) AS ordertotal FROM orderitems GROUP by order_num having sum (quantity * item_price)> = 50 order by ordertotal;