1, an overview of the

“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 areas” and then conduct data processing for several “small areas”.

2. Original table

3, Group By

Example 1

Select category, sum as sum from A group by categoryCopy the code

The result is shown in the following table, which is actually a summary by category.

4, Group By and Order By

Example 2

Select sum from A group by category order by sum descCopy the code

The following table shows the results

“Order by sum desc” cannot be used in Access, but it can be used in SQL Server.

5, Group By Select specified field limit

Example 3

Select sum from A group by category order by category descCopy the code

In example 3, an error message is displayed, as shown in the following figure. The fields specified in the SELECT statement must either be included after the Group By statement as the basis for grouping. Or it has to be included in the aggregate function.

6, Group By All

Example 4

Select sum, sum from A group by allCopy the code

In Example 4, you can specify the Digest field because the multi-column grouping contains the digest field, which results in the following table

“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, group by All will be deleted in future versions of Microsoft SQL Server 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 the above SQL Server can be written in Access

Select sum, sum from A group by category, summaryCopy the code

7, Group By and aggregate function

In example 3, it is mentioned that the select field specified in the group by statement must be “group by field”. Other fields must be included in the aggregate function if they want to appear in the SELECT. Common aggregate functions are as follows:

function role supportive
The sum (column name) sum 　　　　
Max (column name) The maximum 　　　　
Min (column name) The minimum value 　　　　
Avg (column name) The average 　　　　
First (column name) First Record Only the Access support
The last (column name) Last record Only the Access support
Count (column name) Statistical record number Notice the difference with count(*)

Example 5: Average each group

Select avg AS average from A group by avg;Copy the code

Example 6: Find the number of records in each group

Select count(*) AS count from A group by count;Copy the code

Example 7: Find the number of records in each group

8, Having Having the difference with Where

  • The WHERE clause is used to remove rows that do not meet the WHERE criteria before grouping the query results. That is, data is filtered before grouping. The WHERE criteria cannot contain a clustering function, and specific rows are filtered using the WHERE criteria.
  • Having clauses are used to filter groups that meet criteria. Conditions often include a grouping function. Having clauses can also be used to filter groups that meet criteria.

Example 8

Select sum, sum from A group by category having sum > 18Copy the code

Example 9: Combined use of Having and Where

Select SUM(number)from A where number gt; 8 group by category having SUM(quantity) gt; 10Copy the code

9, Compute and Compute By

Select * from A where number > 8Copy the code

Execution Result:

Example 10: Compute

Select * from A where number >8 compute Max,min,avgCopy the code

The result is as follows:

The compute clause can observe the data details of the “query result” or count columns of data (such as Max, min, and AVG in Example 10), returning a select list and compute statistics.

Example 11: Compute By

Select * from A where number >8 order by category compute Max,min,avg by categoryCopy the code

The result is as follows:

Example 11 has more “Order by categories” and “… By category “, the execution results of example 10 are actually displayed in groups (A, B, C), each group is composed of the list of reorganization data and the statistical results of reorganization number, in addition:

  • The compute clause must be used with the order by clause
  • compute… Compared with group BY, group BY can only obtain statistical results of data of each group, but cannot see data of each group

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