Group by and having clauses, and grouphaving clauses

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.