Use of GROUP by in SQL

Source: Internet
Author: User

    • Turn HTTP://WWW.CNBLOGS.COM/RAINMAN/ARCHIVE/2013/05/01/3053703.HTML#M5
    • 1. Overview
    • 2. Original table
    • 3. Simple GROUP BY
    • 4. Group by and Order by
    • 5. The field limit specified by select in Group by
    • 6. Group by all
    • 7. Group by and aggregation functions
    • 8. The difference between having and where
    • 9, Compute and Compute by
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. Example of field restriction specified by select in Group by (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: Find the number of records in 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

Use of GROUP by in SQL

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.