What is the meaning of GROUP BY in SQL query?

What is the meaning of GROUP BY in SQL query?

The meaning of GROUP BY in SQL query is to group data according to specified rules. Its function is to group the query results according to the value of a certain column or multiple columns. Those with equal values ​​are a group. , the GROUP BY statement is generally used in conjunction with aggregate functions.

Group By and Order By

select category, sum(quantity) AS sum of quantities from A group by category order by sum(quantity) desc

"order by sum of quantities desc" cannot be used in Access, but it can be used in SQL Server.

Field restrictions specified by Select in Group By

select category, sum(quantity) as sum of quantities, summary from A group by category order by category desc

After execution, an error will be prompted, as shown below. This is what needs to be noted. The field specified in select must either be included after the Group By statement as the basis for grouping; or it must be included in the aggregate function.

Group By All

select category, summary, sum(quantity) as sum of quantities from A group by all category, summary

中　You can specify the "summary" field. The reason is that "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, you can see that "a, a2001, 13" is the merger of two records, "a, a2001, 11" and "a, a2001, 2".

Although "group by all" is supported in SQL Server, GROUP BY ALL will be deleted in future versions of Microsoft SQL Server 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 above SQL in SQL Server can be written in Access as

select category, summary, sum(quantity) AS sum of quantities from A group by category, summary

Recommended tutorial: "MySQL Tutorial"

The above is the detailed content of What is the meaning of GROUP BY in SQL query?. For more information, please follow other related articles on the PHP Chinese website!