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

This article shows you how to use Group By in SQL. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

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 areas", and then process the data according to several "small areas".

2. Original table

3. Simple Group By example 1

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

The returned results are shown in the table below, which is actually a categorized summary.

4. Example 2 of Group By and Order By

Select category, sum (quantity) sum of AS quantity sum from Agroup by category order by sum (quantity) desc

The returned results are as follows

Sum of order by desc cannot be used in Access, but can be used in SQL Server.

5. Example 3 of the field limits specified by Select in Group By

Select category, sum (quantity) the sum of as quantity, summary from Agroup by category order by category desc

Example 3 prompts the following error after execution, as shown in the following figure. This is the point to note that the fields specified by select are either included at the end of the Group By statement as the basis for grouping or are included in the aggregate function.

6. Group By All example 4

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

In example 4, the "summary" field can be specified because the "multi-column grouping" contains the "summary field", and its execution results are as follows

"Multi-column grouping" is actually grouped according to the merged values of multiple columns (category + summary). In example 4, you can see that "a, a 2001, 13" is the merge of "a, a 2001, 11" and "a, a 2001, 2".

Although "group by all" is supported in SQL Server, GROUP BY ALL will be 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 multi-column grouping is also supported in Access. The SQL in the above SQL Server can be written as

Select category, summary, sum (quantity) sum of AS quantity from Agroup by category, summary

7. Group By and aggregate function

In example 3, it is mentioned that the fields specified by select in the group by statement must be "grouped by fields". Other fields must be included in the aggregate function if they want to appear in the select. The common aggregate functions are as follows:

Function function support sum (column name) summation 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) the last record only Access supports count (column name) statistics the difference between the number of records and count (*)

Example 5: find the average 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, number of count (*) AS records from A group by category

Example 7: calculate the number of records in each group

8. The difference between Having and Where

The function of the where clause is to remove the rows that do not meet the where condition before grouping the query results, that is, to filter the data before grouping, the where condition cannot contain a cluster function, and the where condition is used to filter out specific rows.

The function of the having clause is to filter the groups that meet the criteria, that is, to filter the data after the grouping, which often contains clustering functions, to filter out specific groups using having conditions, or to use multiple grouping criteria for grouping.

Example 8

Select category, sum (quantity) the sum of as quantity, from Agroup by category having sum (quantity) > 18

Combined use of sample 9:Having and Where

Select category, SUM (quantity) from Awhere quantity gt;8group by category having SUM (quantity) gt; 10

9. Compute and Compute By

Number of select * from A where > 8

Execution result:

Sample 10:Compute

Select * from Awhere quantity > 8compute max (quantity), min (quantity), avg (quantity)

The implementation results are as follows:

The compute clause can observe the data details of the "query results" or count each column of data (such as max, min, and avg in example 10), and the returned result consists of a select list and compute statistics.

Sample 11:Compute By

Select * from Awhere quantity > 8order by category compute max, min, avg by category

The implementation results are as follows:

Example 11 has more "order by category" and "... by category" than example 10. The execution result of example 10 is actually displayed by grouping (a, b, c), each group is composed of a list of group data and statistics of the number of groups, and in addition:

The compute clause must be used with the order by clause

Compared with group by, compute...by can only get the statistical results of each group of data, but can not see each group of data.

Compute and compute by do not play an important role in actual development. SQL Server supports compute and compute by, but Access does not.

The above is how to use Group By in SQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow 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.