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

Editor to share with you how to use group by in the sql sentence, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. Overview

Group by is to group data according to the rules behind by. The so-called grouping means that the data set is divided into several 'groups' and processed against several groups.

two。 rule of grammar

SELECT column_name, aggregate_function (column_name) FROM table_nameWHERE column_name operator valueGROUP BY column_name

3. Give examples to illustrate

We have an order form like this:

We want to count the total amount of each user's order, which can be achieved with the help of group by:

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 grouped according to Customer, then the sum of each group of orderPrice is counted, and finally a group is displayed as a line.

If you remove the group by statement, you will get the following result:

It's about adding all the price together to get the final sum, which is different from what we expected.

4. Pay attention

If the following words appear:

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

An error will be reported because the field of select is either included after the group by as a basis for grouping or in the aggregate function, otherwise the column of the summary does not match to a row.

Therefore, it can be written as follows:

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

If you want to use multi-column grouping, you can use group by all syntax:

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

The difference between 5.where and having

Where is to remove rows that do not meet the where condition before querying and grouping, that is, the data is filtered by grouping money, and the aggregate function cannot be included in the where condition.

Having is to filter groups that meet the criteria, that is, to filter data after grouping, and having statements often contain aggregate functions to filter out specific groups with having.

For example:

Select Customer,sum (OrderPrice) as total_price group by Customer having total_price > 1700; these are all the contents of the article "how to use group by in sql sentences". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.