Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the use of groupby statements in mysql". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. Overview

"Group By" literally means grouping data according to the rules specified by "By." The so-called grouping is to divide a "data set" into several "small areas" and then process data for several "small areas."

2. Original table 3. Simple Group By

Examples 1

select category, sum(Quantity) as sum de quantités from Agroup by Category

The results are shown in the table below, which is actually a sub-summary.

Group By and Order By

Example 2

select category, sum(Quantity) AS sum de quantités from Agroup by category order by sum(Quantity) desc

The following table returns results: "order by sum of quantities desc" is not allowed in Access, but it is allowed in SQL Server.

5. Field restrictions specified in Select in Group By

select category, sum(quantity) as sum de quantités, abstract from Agroup by category order by category desc

Example 3 will prompt an error after execution, as shown in the following figure. This is the point to note that the fields specified in select are either included after the Group By statement as the basis for grouping or included in the aggregate function.

6、Group By All

Example 4

select category, summary, sum as sum of quantities from Agroup by all categories, summary

In Example 4, you can specify the Summary field because the Summary field is included in Multi-column Grouping. The execution results are as follows: Multi-column Grouping is actually grouping according to the combined values of multiple columns (category + summary). In Example 4, you can see that "a, a2001, 13" is the combination of "a, a2001, 11" and "a, a2001, 2."

Although "group by all" is supported in SQL Server, future versions of Microsoft SQL Server will remove GROUP BY ALL to avoid using GROUP BY ALL in new development work. Access does not support "Group By All", but Access also supports multi-column grouping. SQL in SQL Server can be written as

select category, summary, sum from Agroup by category, summary

7. Group By and aggregate functions

In Example 3, it is mentioned that the field specified in select in group by statement must be "grouping by field", and other fields must be included in aggregate function if they want to appear in select. Common aggregate functions are as follows:

sum(column name) sum max(column name) max min(column name) min avg(column name) average first(column name) first record Access only last(column name) last record Access only count(column name) statistics records Note the difference between count and count(*)

Example 5: Average each group

select category, avg(quantity) AS average from A group by category;

Example 6: Find the number of records in each group

select category, count(*) AS number of records from A group by category;

Example 7: Find the number of records in each group

Difference between Having and Where

The where clause is used to remove rows that do not meet the where condition before grouping query results, that is, to filter data before grouping. The where condition cannot contain grouping functions, and specific rows are filtered out using the where condition. The function of the having clause is to filter the groups that meet the conditions, that is, filter the data after grouping. The conditions often include clustering functions. Use the having condition to filter out specific groups. You can also use multiple grouping criteria to group. Example 8

select category, sum(Quantity) as sum de quantités from Agroup by category having sum(Quantity) > 18

Example 9: Having and Where combined

select category, SUM from Awhere gt;8group by category having SUM gt; 10

Compute and Compute By

select * from A where Quantity> 8

Execution Results: Example 10: Compute

select *from Awhere Qty>8compute max(Qty),min(Qty),avg(Qty)

The result of execution is as follows: The compute clause can observe the data details of the "query result" or count the column data (for example, max, min, and avg in Example 10). The returned result consists of the select list and the compute statistics.

Example 11: Compute By

select *from Awhere Qty>8order by category compute max(Qty),min(Qty),avg(Qty) by category

The execution results are as follows: Example 11 has more "order by category" and "... by category" than Example 10. The execution results of Example 10 are actually displayed according to groups (a, b, c), each group is composed of reorganization data list and reorganization number statistics. In addition:

Compute clause must be used together with order by clause Compute... by Compared with group by, group by can only get statistical results of each group of data, but it cannot be seen that the effect of compute and compute by in actual development is not very large. SQL Server supports compute and compute by, but Access does not support them.

"The use of groupby statements in mysql" is introduced here. Thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.