Group by and having clauses, and grouphaving clauses
Group by clause
In some cases. You need to divide the rows in a table into multiple groups, and then use the group as a whole to obtain information about the reorganization, such as the number of employees in each department, or the average salary of an employee in a department. In this case, you need to use the group by clause to group the data in the table.
The group by clause can be used to group data rows in one or several queues of a table. Multiple columns are separated by commas (,). If multiple columns are grouped, oracle groups the data based on the first column, groups the data in the group based on the second column, and so on.
After grouping data, we mainly use aggregate functions to collect statistics on grouped data.
The specific operations are as follows:
SQL> select empno, deptno from emp;
EMPNO DEPTNO
--------------------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
7935 20
As shown in the data above, the value of deptno is only 10, 20, 30, so if we want to count the number of people in each department of 10, 20, 30, you can use the following statement:
SQL> select deptno "department number", count (*) "department count" from emp group by deptno;
Department No., department count
--------------------
30 6
20 6
10 3
We can also calculate the average salary of each department as follows:
SQL> select deptno, avg (sal) from emp group by deptno;
Deptno avg (SAL)
--------------------
30 1708.33333
20 2645.83333
10 2916.66667
Use of group by and having.
Having clause
The having clause is usually used with the group by clause. After the grouping results are collected, you can use the having clause to further filter the grouping results.
A having clause can contain up to 40 expressions. The expressions in the having clause are separated by the keyword "and" or.
As shown in the following figure, for departments that have been grouped, we select departments with more than 5 members.
SQL> select deptno "department number", count (*) "department count" from emp group by deptno having count (*)> 5;
Department No., department count
--------------------
30 6
20 6
Select a department with a team ID greater than 10.
SQL> select deptno, avg (sal) from emp group by deptno having deptno> 10;
Deptno avg (SAL)
--------------------
30 1708.33333
20 2645.83333