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

In this issue, Xiaobian will bring you about how to understand the usage of Group By in SQL. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

The GROUP BY statement is used in conjunction with an aggregation function to group the result set according to one or more columns.

1. Overview

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

2. Original table

Simple Group By

Examples 1

select category, sum(Quantity) as sum de quantités from A group by category

The returned 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 A group by category order by sum(Quantity) desc

The results are as follows

"order by sum desc" is not allowed in Access, but it is allowed in SQL Server.

5. Field restrictions specified in Select in Group By

Example 3

select category, sum(quantity) as sum de quantités, abstract from A group 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(quantity) as sum of quantities from A group by all categories, summary

In Example 4, you can specify the Summary field because the Summary field is included in the Multi-column Grouping. The execution results are shown in the table below.

"Multi-column grouping" is actually grouping according to the combined values of multiple columns (category + abstract). 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 A group 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:

function

role

supportive

sum(column name)

summing

max(column name)

maximum value

min(listed)

minimum value

avg(listed)

average

first(listed)

the first record

Access support only

last

the last record

Access support only

count(column name)

Number of statistical 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 a grouping function. Use the where condition to filter out specific rows.·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

Implementation results:

Example 10: Compute

select *from A where Qty>8 ompute max(Qty),min(Qty),avg(Qty)

The results are as follows:

The compute clause allows you to observe the data details or statistics of the "query results" column data (for example, max, min, and avg in 10), and the return result consists of a select list and compute statistics.

Example 11: Compute By

select *from A where Quantity>8 order by category compute max(Quantity),min(Quantity),avg(Quantity) by category

The results are as follows:

Example 11 has more "order by category" and "..." than Example 10. By category ", 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, and in addition:

·Compute clause must be used with order by clause·compute... Compared with group by, group by can only get statistical results of each group of data, but can not see each group of data.

Compute and compute by are not very important in actual development. SQL Server supports compute and compute by, but Access does not.

The above is how to understand the usage of Group By in SQL shared by Xiaobian for everyone. If you happen to have similar doubts, you may wish to refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

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.