Use of group by in SQL

ArticleDirectory

• 1. Overview
• 2. Original table
• 3. Simple group
• 4. Group by and order
• 5. Field restrictions specified by select in group
• 6. Group by all
• 7. Group by and Aggregate functions
• 8. Differences between having and where
• 9. compute and compute

1. Overview

Literally, "group by" refers to grouping data according to the rules specified by "by". The so-called grouping refers to dividing a "dataset" into several "small areas ", then data is processed for several "small areas.

2. Original table

3. Simple group by Example 1

 
Select category, sum (Quantity) as quantity sum from agroup by category
 

The following table lists the returned results.

4. Example 2 of group by and order

 
Select category, sum (Quantity) as quantity sum from agroup by category order by sum (Quantity) DESC
 

The returned results are as follows:

"DESC" cannot be used in access, but "DESC" can be used in SQL Server.

5. Example 3 of the field restriction specified by select in group

Select category, sum (Quantity) as quantity sum, abstract from agroup by category order by category DESC
 

Example 3: An error is prompted after execution, as shown in. Note that the field specified in select is eitherIt must be included after the group by statement as the basis for grouping; or it must be included in the aggregate function..

6. Group by all Example 4

 
Select class, abstract, sum (Quantity) as quantity sum from agroup by all class, abstract
 

In Example 4, the "summary" field can be specified because "Multi-column grouping" contains the "summary field". The execution results are as follows:

"Multi-column grouping" is actually based on multiple columns (CATEGORY + Summary) The merged values are grouped. In example 4, we can see that "a, a2001, 13" is "a, a2001, 11" and "A, a2001, 2 "merge two records.

Although SQL Server supports "group by all", Microsoft SQL server will delete group by all in future versions to avoid using group by all in new development work. Access does not support "group by all", but access also supports multi-column grouping. The SQL statements in the preceding SQL Server can be written

 
Select category, abstract, sum (Quantity) as quantity sum from agroup by category, abstract
 

7. Group by and Aggregate functions

In Example 3, the field specified by the SELECT statement in the group by statement must be a "group by field". If other fields come up with the SELECT statement, they must be included in the aggregate function, the following table lists common Aggregate functions:

Function	Function	Support
Sum (column name)	Sum
Max (column name)	Maximum Value
Min (column name)	Minimum value
AVG (column name)	Average Value
First (column name)	First Record	Only access supported
Last (column name)	Last Record	Only access supported
Count (column name)	Number of statistical records	Note the difference with Count (*)

Example 5: calculate the average value of each group

 
Select category, AVG (Quantity) as average from a group by category;
 

Example 6: calculate the number of records in each group

 
Select category, count (*) as record count from a group by category;
 

Example 7: calculate the number of records in each group. 8. Differences between having and where

• The where clause is used inBefore grouping query resultsRemove rows that do not meet the where condition, that is, filter data before grouping,The where condition cannot contain clustering functions.Use the where condition to filter out specific rows.
• The having clause is used to filter groups that meet the conditions, that isFilter data After grouping. conditions often contain clustering functions., Using the having ConditionFilter out a specific group, You can also use multiple grouping standards for grouping.

Example 8

 
Select class, sum (Quantity) as quantity sum from agroup by class having sum (Quantity)> 18
 

Example 9: joint use of having and where

 
Select category, sum (Quantity) from awhere quantity gt; 8 group by category having sum (Quantity) gt; 10
 

9. compute and compute

 
Select * From A Where quantity> 8
 

Execution result:

Example 10: compute

 
Select * From awhere quantity> 8 compute max (Quantity), min (Quantity), AVG (Quantity)
 

The execution result is as follows:

The compute clause can observe the data details of the query results or collect statistics on each column (for example, Max, Min, and AVG in Example 10). The returned results are composed of the select list and compute statistical results.

Example 11: Compute

 
Select * From awhere quantity> 8 orderCategoryCompute max (Quantity), min (Quantity), AVG (Quantity)Category
 

The execution result is as follows:

In Example 11, "order by category" and "... by category ", the execution result of Example 10 is displayed by group (A, B, C). Each group is composed of the reorganization data list and the number of reorganization statistics. In addition:

• The compute clause must be used with the order by clause.
• Compute... By compared with group by, group by can only obtain the statistical results of each group, but cannot see the data of each group.

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