A brief discussion on the use of GROUP BY and HAVING in SQL statements

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, etc. 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 is applied to the population field of all returned records, resulting in the query returning only one result, the total population of all countries.

Having is the screening condition after grouping (group by), and the grouped data is further screened within the group
where is to filter before grouping

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

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

That is to say, all fields in the table except region can only return a value after being calculated by aggregate functions such as SUM and COUNT.

The HAVING clause allows us to filter the groups of data.

The WHERE clause filters the records before aggregation. That is to say, it works before the GROUP BY clause and the HAVING clause.

The HAVING clause filters the group records after aggregation.

Let us understand the GROUP BY and HAVING clauses through specific examples, and use the bbc table introduced in Section 3.

SQL Example:

1. Display the total population and total area of ​​each region.

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region

First, use region to divide the returned records into multiple groups. This is the literal meaning of GROUP BY. After the groups are formed, aggregate functions are then used to operate on different fields in each group (one or more records).

2. Display the total population and total area of ​​each region. Only those regions with an area greater than 1,000,000 are displayed.

SELECT region, SUM(population), SUM(area)

FROM bbc8 F4 w2 v( P- f
GROUP BY region
HAVING SUM(area)>1000000#

Here, we cannot use where to filter regions with more than 1,000,000, because such a record does not exist in the table.
On the contrary, the HAVING clause allows us to filter the groups of data after grouping.

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

select c.name, count(order_number) as count from orders o,customer c where c.id=o.customer_id group by customer_id;

+--------+-------+
| name | count |
+--------+-------+
| d | 9 |
| cc | 6 |
| Bodhi seeds | 1 |
| cccccc | 2 |
+--------+-------+

Add HAVING filter

select c.name, count(order_number) as count from orders o,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 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 the academic performance of 4 students in each subject, including SNO (student number), PNO (course name), and GRADE (grade).

1. Display the course names and scores of students with scores above 90

//This is a simple query and does not use 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

7 rows selected.

2. Display the number of subjects in which each student's score is above 90 points

//Display in groups and count according to the where condition SQL> select sno,count(*) from sc where grade>=90 group by sno;

 SNO COUNT(*)
---------- ----------
  1 3
  twenty two
  4 1
  3 1

3. We did not use the having statement here. Next, if we want to select three good students, the condition is that at least two courses are above 90 points to be eligible. List the eligible student numbers and the number of courses above 90 points.

// Display in groups, count according to the where condition, and filter the groups according to the having clause SQL> select sno,count(*) from sc where grade>=90 group by sno having count(*)>=2;

 SNO COUNT(*)
---------- ----------
  1 3
  twenty two

This result is what we want. It lists the student numbers who are eligible for the "Three Good Students" award. Compared with the previous example, we can see that this is a subquery performed after grouping.

4. The school is selecting advanced students. Students with an average score of more than 90 points are eligible, and the Chinese course score must be above 95 points. Please list the eligible students.

//In fact, this query first extracts the student numbers whose Chinese scores are greater than 95, then calculates the average, and then selects the students whose average scores are greater than 90 according to the having statement after grouping. 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. Query the student ID and average score of students whose average score is at least higher than the average score of student ID 3

//Comparisons and subqueries can be performed in the having clause SQL> select sno,avg(grade) from sc
 group by sno
 having avg(grade) > (select avg(grade) from sc where sno=3);

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.