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

This article introduces what grouping functions are in the Oracle database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. The use of group by

-grouping according to DEPTNO and JOB. Seek the sum of wages of employees with the same DEPTNO and the same JOB.

SELECT E.DEPTNOGENE E.Job ORDER BY E.DEPTNO sum (E.SAL) FROM EMP E GROUP BY E.DEPTNOREE E.Job

2. Group by with the use of rollup

Rollup ()-one or more parameters can be used. It means to summarize the data from right to left and generate a row. Rollup is a statistical function.

The following is the statistics according to the grouping situation, and finally a full summary.

(1) simply use rollup-- to generate a new row of data. (to generate a new row of data, you can also use UNION ALL)

SELECT D.DUMMY FROM DUAL D GROUP BY ROLLUP (D.DUMMY)

(2) grouping according to E.DEPTNOJOB, and then from right to left.

SELECT E.DEPTNODON E.Job ORDER BY E.DEPTNO sum (E.SAL) FROM EMP E GROUP BY ROLLUP (E.DEPTNOJOB) ORDER BY E.DEPTNO

Understanding of the above results of using ROLLUP:

A: first of all, 9 pieces of data are queried according to GROUP BY E.DEPTNOGEN E. job (except for 4pc8 and 12JOB). According to the definition of rollup, the parameters in ROLLUP are subsumed from right to left.

First of all, according to JOB (summary of all the JOB), summarize 12 lines of 4jing8 DEPTNO, and then summarize the 13th row of data according to E.DEPTNO (summary of all DEPTNO).

(3) Special circumstances

SELECT E.DEPTNODON E.JOBJEI SUM (E.SAL) FROM EMP E GROUP BY ROLLUP (E.JOBMIT E.DEPTNO) ORDER BY E.DEPTNO

Understanding: first of all, query the first nine pieces of data according to GROUP BY E.DEPTNOJOB, and then summarize the E.DEPTNO, but you must consider the sum of all the wages of JOB, that is, the same JOB, so the following five pieces of data appear.

3. The use of group by in conjunction with cube SELECT E.DEPTNODORO E.Job ORDER BY E.DEPTNO sum (E.SAL) FROM EMP E GROUP BY CUBE (E.DEPTNOREE E.Job) ORDER BY E.DEPTNO

Understanding: CUBE makes a separate summary of each condition in the condition: that is, a summary of individual columns

GROUP BY CUBE (E.DEPTNograd E. job) first queries the data according to: GROUP BY E.DEPTNograd E.JOB, and then summarizes the E.JOB (does not consider DEPTNO, separately, while ROLLUP is under the same DEPTNO), and then summarizes the E.DEPTNO, and finally all of it.

Cube (a), (b), (b), () cube (a), (a), (b), () cube () the statistical series includes: (a), (a), (c), (4), use of GROUPING.

The GROUPING function can take a column and return either 0 or 1. GROUPING () returns 1 if the column value is empty, and 0 if the column value is not empty. GROUPING can only be used in queries that use ROLLUP or CUBE. GROUPING () is useful when you need to display a value where a null value is returned.

SELECT GROUPING (E.DEPTNO), E.DEPTNOGRING E.JOBJEREM (E.SAL) FROM EMP E GROUP BY ROLLUP (E.DEPTNODON E.JOB) ORDER BY E.DEPTNO

You can use the decode or case function to convert this unfriendly display:

SELECT CASE WHEN grouping (E.DEPTNO) = 1 THEN 'Total' ELSE E.DEPTNO | |''END AS department, CASE WHEN grouping (E.JOB) = 1 AND grouping (E.DEPTNO) = 0 THEN' subtotal 'ELSE E.JOB END AS type of work, SUM (E.SAL) FROM EMP E GROUP BY ROLLUP (E.DEPTNOJOB) ORDER BY E.DEPTNO

SELECT DECODE (GROUPING (E.DEPTNO), 1, 'total', E.DEPTNO) AS department, CASE WHEN GROUPING (E.JOB) = 1 AND GROUPING (E.DEPTNO) = 0 THEN 'subtotal' ELSE E.JOB END AS type of work, SUM (E.SAL) FROM EMP E GROUP BY ROLLUP (E.DEPTNO, E.JOB) ORDER BY E.DEPTNO

5. Grouping sets provides the function of specifying summary collection conditions.

The data are summarized according to E.DEPTNOREE E.JOB.

SELECT E.DEPTNODON E.JOBJEREM (E.SAL) FROM EMP E GROUP BY GROUPING SETS (E.DEPTNOJOB)

About which grouping functions in the Oracle database are shared here, I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.