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

Editor to share with you how to use GROUP BY and HAVING in sql sentences, 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!

Before introducing the GROUP BY and HAVING clauses, we must first talk about a special function in the sql language: aggregate functions

For example, SUM, COUNT, MAX, AVG and so on. The fundamental difference between these functions and other functions is that they generally act on multiple records.

SELECT SUM (population) FROM bbc

The SUM here acts on all the population fields that return records, and the result is that the query returns only one result, that is, all

The total population of the country.

Having is the filter condition after grouping (group by), and then filtered within the grouped data group.

Where is screened before grouping.

By using the GROUP BY clause, you can make functions such as SUM and COUNT work on data that belongs to a group.

When you specify GROUP BY region, a set of data belonging to the same region will only return one row of values.

In other words, all fields except region (region) in the table can only be returned by SUM, COUNT and other aggregate functions.

The HAVING clause allows us to filter the grouped data.

The WHERE clause filters the records before aggregating. That is, before the GROUP BY clause and the HAVING clause.

The HAVING clause filters the group records after aggregation.

Let's understand the GROUP BY and HAVING clauses through concrete examples, and use the bbc table introduced in section 3.

SQL instance:

First, display the total population and total area of each area.

SELECT region, SUM (population), SUM (area) FROM bbcGROUP BY region

First, the returned records are divided into groups with region, which is the literal meaning of GROUP BY. After grouping, the aggregate function is then used to operate on different fields (one or more records) in each group.

Second, display the total population and total area of each region. Only those areas with an area over 1000000 are shown.

SELECT region, SUM (population), SUM (area) FROM bbc8 F4 w2v (P-fGROUP BY regionHAVING SUM (area) > 1000000

Here, we cannot use where to filter more than 1000000 regions, because such a record does not exist in the table.

On the contrary, the HAVING clause allows us to filter groups of data

3. Query the number of users' orders in CUSTOMER and ORDER tables

Select c.name, count (order_number) as count from orders customer c where c.id=o.customer_id group by customer_id;+-+-+ | name | count | +-+-+ | d | 9 | cc | 6 | Bodhi | 1 | | cccccc | 2 | +-+-+

Add HAVING filtering

Select c.name, count (order_number) as count from orders customer c where c.id=o.customer_id group by customer_id having count (order_number) > 5 | name | count | +-+-+ | d | 9 | | cc | 6 | +-+-+

4. I will give you some more examples.

SQL > select * from sc; SNO PNO GRADE- 1 YW 95 1 SX 98 1 YY 90 2 YW 89 2 SX 91 2 YY 92 3 YW 85 3 SX 88 3 YY 96 4 YW 95 4 SX 89 SNO PNO GRADE- 4 YY 88

This table describes the records of four students' grades in each subject, including SNO (student number), PNO (course name), and GRADE (grade).

1. Show the course names and scores of students with a score of more than 90

/ / this is a simple query and does not use a grouping query

SQL > select sno,pno,grade from sc where grade > = 90; SNO PNO GRADE- 1 YW 95 1 SX 98 1 YY 90 2 SX 91 2 YY 92 3 YY 96 4 YW 95

Seven rows have been selected.

2. Show the number of students whose scores are above 90.

/ / display in groups, and count SQL > select sno,count (*) from sc where grade > = 90 group by sno; SNO COUNT (*)-1 3 2 2 4 1 3 3 1 after where conditions

3. We do not use the having sentence here. Next, if we want to select three good students, the condition is that at least two courses are above 90 points in order to be eligible, list the number of qualified students and the number of courses with more than 90 points.

/ / display the grouping, and count according to the where condition, and filter the grouping SQL > select sno,count (*) from sc where grade > = 90 group by sno having count (*) > = 2 according to the having clause; SNO COUNT (*)-1 322

This result is what we want. it lists the student numbers that are qualified to select the three good students. after comparing with the previous example, it is found that this is a sub-query after grouping.

4. The school selects advanced students. Students with an average score of more than 90 are required to be qualified, and the Chinese course must be above 95. Please list the qualified students.

/ / in fact, this query first extracts the student numbers whose language scores are greater than 95, and then calculates the average. According to the group display, SQL > select sno,avg (grade) from sc where SNO IN (SELECT SNO FROM SC WHERE GRADE > = 95 AND PNO='YW') group by sno having avg (grade) > = 90; SNO AVG (GRADE)-1 94.3333333 4 90.6666667

5. the student number and the average score of the student whose average grade is at least higher than the student number is 3

Comparisons can be made in the / / having clause and subquery SQL > select sno,avg (grade) from sc group by sno having avg (grade) > (select avg (grade) from sc where sno=3); these are all the contents of the article "how to use GROUP BY and HAVING 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.