Explain the Group By usage in SQL, and explain sqlgroupby

Explain the Group By usage in SQL, and explain sqlgroupby

The group by statement is used in combination with the aggregate function to GROUP result sets based on one or more columns.

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

Example 1

Select class, sum (Quantity) as quantity sum from A group by class

The following table lists the returned results.

4. Group By and Order

Example 2

Select category, sum (Quantity) AS quantity sum from A group 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. Field restrictions specified By Select in Group

Example 3

Select category, sum (Quantity) as quantity sum, abstract from A group by category order by category desc

Example 3: An error is prompted after execution, as shown in. This is worth noting that the field specified By select must either be included after the Group By statement as the basis for grouping, or be included in aggregate functions.

6. Group By All

Example 4

Select class, abstract, sum (Quantity) as quantity sum from A group 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 grouping by the combined values of multiple columns (Category + abstract). In example 4, we can see that "a, a2001, 13" is "a, a2001, the combination of two records: "11" and "a, a2001, and" 2.

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 A group 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:Average value of each group

Select category, avg (Quantity) AS average from A group by category;

Example 6:Calculate the number of records of each group

Select category, count (*) AS record count from A group by category;

Example 7:Calculate the number of records of each group

8. Differences between Having and Where

• The where clause removes rows that do not meet the where condition before grouping the query results. That is, data is filtered 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 is, filter data after the group. The conditions often contain clustering functions and use the having condition to filter out specific groups, you can also use multiple grouping standards for grouping.

Example 8

Select class, sum (Quantity) as quantity sum from
Group by type
Having sum (Quantity)> 18

Example 9: Joint use of Having and Where

Select category, SUM (Quantity) from
Where quantity gt; 8
Group by type
Having SUM (Quantity) gt; 10

9. Compute and Compute

Select * from A where quantity> 8

Execution result:

Example 10: Compute

Select * from A where quantity> 8 ompute 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 A where quantity> 8 order by category compute max (Quantity), min (Quantity), avg (Quantity) by 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.

Articles you may be interested in:

• Mssql CASE, group by usage
• Usage of mysql when filtering group by combinations of Multiple Fields
• Analysis of group by usage in SQL statements
• Summary of SQL Server group by usage
• Comparison and usage of distinct and group by statements in MySQL