Briefly explain the use of group by in sql statements

1. Overview

Group by means to group data according to the rules after by. The so-called grouping means dividing the data set into several "small groups" and processing them accordingly.

2. Grammatical rules

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

3. Examples

We have an order table like this:

We want to count the total amount of each user's order, we can use group by to achieve this:

select Customer,sum(OrderPrice) as total_price group by Customer;

The following result set appears:
Bush 2000
Carter 1700
Adams 2000
In fact, the data is first grouped by Customer, then the sum of orderPrice of each group is calculated, and finally each group is displayed as a row.
If you remove the group by statement, the following results will appear:

It is about adding up all the prices to get the final total, which is different from our expectations.

4. Note

If the following appears:

select category, sum(quantity) as sum of quantity, summaryfrom Agroup by categoryorder by categorydesc

An error will be reported because the selected field must either be included after group by as the basis for grouping, or be included in an aggregate function, otherwise the summary column cannot match a row.
Therefore, the following can be used:

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

If you want to group by multiple columns, you can use the group by all syntax:

select Customer,OrderDate,sum(OrderPrice) group by all Customer,OrderDate

5. The difference between where and having

where is to remove rows that do not meet the where condition before query grouping, that is, filter data before grouping. The where condition cannot contain aggregate functions.
Having is to filter the groups that meet the conditions, that is, to filter the data after grouping. The having statement often contains aggregate functions, and having is used to filter out specific groups.
Example:

select Customer,sum(OrderPrice) as total_price group by Customer having total_price>1700;

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.