The system organizes the use of group by in SQL with group by multiple field limits

When do I use GROUP by in SQL? This article explains in detail the use of group by, which is simply defined as dividing a "dataset" into several "small areas" and then processing data for several "small regions". What are the restrictions on the fields specified by select in Group by? Apache PHP MySQL

1. Overview

"GROUP BY" is the literal understanding that the data is grouped according to the rules specified by the "by", in which the so-called grouping is dividing a "dataset" into several "small regions" and then processing data for several small regions.

2. Original table

3. Simple GROUP BY

Example 1

Select category, sum (quantity) as number of and from Agroup by category

The result is the following table, which is actually a subtotal.

4. Group by and Order by

Example 2

Select category, sum (quantity) as number of and from Agroup by category order by sum (quantity) desc

Returns the results as shown in the table

"ORDER BY quantity and DESC" can not be used in Access, but in SQL Server.

5. The field limit specified by select in Group by

Example 3

Select category, sum (quantity) as number of sums, summary from Agroup by category order BY category Desc

Example 3 will prompt for an error after execution, such as. This is a point to note that the field specified in select is either to be included in the group by statement, as a basis for grouping, or to be included in an aggregate function.

6. Group by all

Example 4

Select category, Summary, sum (quantity) as number of and from Agroup by all category, summary

In Example 4, you can specify a summary field because the "summary field" is included in multi-column grouping, and the result is the following table

"Multi-column grouping" is actually the combination of Dole (category + summary) combined values, in example 4 you can see "A, a2001, 13" for "A, a2001, 11" and "A, a2001, 2" Two records of the merge.

Although group by all is supported in SQL Server, group by IS removed in future versions of Microsoft SQL Server to avoid using group by all in new development work. Group BY all is not supported in Access, but multiple column groupings are also supported in Access, and SQL in SQL Server above can be written in Access

Select category, Summary, sum (quantity) as number of and from agroup by category, summary

7. Group by and aggregation functions

In Example 3, the reference to the group by statement in the Select field must be a "group by field", and the other fields must be included in the aggregate function if they want to appear in the Select, the following table is the common aggregate function:

function	function	support of
SUM (column name)	Sum
Max (column name)	Maximum Value
Min (column name)	Minimum value
AVG (column name)	Average
First (column name)	First record	Only access supports
Last (column name)	Last record	Only access supports
Count (column name)	Number of statistics records	Note the difference between the count (*) and

Example 5: Finding the average of each group

Select category, AVG (quantity) as average from A Group by category;

Example 6: Finding the number of records for each group

Select category, COUNT (*) as record number from A Group by category;

Example 7: Finding the number of records for each group

8. The difference between having and where

• The purpose of the WHERE clause is to remove rows that do not conform to the where condition before grouping the results of the query, that is, filtering the data before grouping, where the cluster function cannot be included, and the Where condition is used to filter out specific rows.

• The HAVING clause is used to filter groups that satisfy a condition, that is, to filter the data after grouping, often with clustering functions, to filter out specific groups using the having condition, or to group by using multiple grouping criteria.

Example 8

Select category, sum (quantity) as number of and from agroup by category having sum (qty) > 18

Example 9:having and where's combined use method

Select category, sum (quantity) from Awhere quantity Gt;8group by category having SUM (quantity) GT; 10

9, Compute and Compute by

SELECT * from A where quantity > 8

Execution Result:

Example 10:compute

Select *from awhere quantity >8compute max (quantity), min (quantity), Avg (qty)

The results of the implementation are as follows:

The COMPUTE clause is able to observe the data details of the query results or to count the column data (such as Max, Min, and Avg in Example 10), and the returned results are composed of the select list and the compute statistic results.

Example 11:compute by

Select *from Awhere quantity >8order by category compute MAX (quantity), min (quantity), AVG (quantity) by category

The results of the implementation are as follows:

Example 11 compares the "Order by category" and the "... by category" as compared to example 10, and the results of example 10 are actually displayed in groups (A, B, c), with each group composed of the reorganization data list and the reorganization statistics results, plus:

• The COMPUTE clause must be used with the ORDER BY clause

• Compute...by GROUP by can only get statistical results for each group of data, rather than

The role of compute and compute by is not very large in real-world development, SQL Server supports compute and compute by, and access does not support

Related articles:

SQL GROUP BY Statement usage

SQL GROUP BY syntax and instance

Related videos:

SQL Fun class