Learn from Chapter 5 of Oracle development art of breaking through the iceberg GROUP BY
For simple group by statements, it is difficult to analyze the complex dimensions, and it is difficult to meet the requirements of the actual production of complex reports. The extension feature of group by is needed. The union statement can also meet the requirements, but the sql is complex and inefficient
1 rollup multidimensional summary
Rollup: for grouping, general grouping is performed first, and then on this basis, by moving the column from right to left, and then making a higher level subtotal, and finally summing up, pay attention to the correlation between rollup grouping and column order
Specify n columns with n+1 grouping methods
Some rollup s can remove some unnecessary subtotals and totals
Example
[oracle@localhost ~]$ sqlplus scott/tiger;
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 23 10:31:24 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:31:24 SCOTT@edw> set autotrace on
10:31:30 SCOTT@edw> SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY ROLLUP(a.dname,b.job);
DNAME JOB SUM_SAL
-------------- --------- ----------
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES Nine thousand and four hundred
RESEARCH CLERK 1900
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975
RESEARCH Ten thousand eight hundred and seventy-five
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING Eight thousand seven hundred and fifty
29025
13 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
913 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
13 rows processed
10:31:34 SCOTT@edw>
It can be seen that only the dept and emp tables are scanned once, but if it is written by union, it will be repeatedly scanned, which is inefficient
A hidden operation SORT GROUP BY ROLLUP can be seen through the execution plan. The display result is in order. Generally, sorting is still needed. The default sorting does not necessarily meet the business requirements
Roll up grouping has directionality
If you use hint: expand? GSET? To? Union, the optimizer will convert the rollup to the corresponding union all operation, and other grouping sets and cube s can also
Part of the rollups are grouped. You can take the columns that do not need subtotals out of the rollups and put them into group by. Of course, there is no total
Example
10:31:34 SCOTT@edw> set autotrace off 10:43:49 SCOTT@edw> SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY to_char(b.hiredate,'yyyy'),a.dname,ROLLUP(b.job); HIRE DNAME JOB SUM_SAL ---- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1980 RESEARCH 800 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1981 SALES 9400 1981 RESEARCH ANALYST 3000 1981 RESEARCH MANAGER 2975 1981 RESEARCH 5975 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 ACCOUNTING 7450 1982 ACCOUNTING CLERK 1300 1982 ACCOUNTING 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000 1987 RESEARCH 4100 17 rows selected. Elapsed: 00:00:00.01 10:43:53 SCOTT@edw>
2 cube cross Report
cube grouping can realize more detailed and complex statistics, analyze all possible factors of different dimensions, and generate cross reports. cube grouping is to aggregate from n columns first, that is, one column is not taken, then subtotal, that is, one column is taken to n-1 column, and finally all N columns are taken, that is, standard grouping
Because it contains all possible combinations, the result has nothing to do with the order of the columns. The order of the columns only affects the default hidden sort. If the display sort is used, it doesn't matter
Add a column to the cube group, which may result in exponential growth, n-power of group type 2
Similar syntax, example
11:02:40 SCOTT@edw> set autotrace on
11:02:48 SCOTT@edw> SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY CUBE(a.dname,b.job);
DNAME JOB SUM_SAL
-------------- --------- ----------
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
SALES 9400
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
RESEARCH 10875
RESEARCH CLERK 1900
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975
ACCOUNTING 8750
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
18 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2382666110
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 392 | 7 (29)| 00:00:01 |
| 1 | SORT GROUP BY | | 14 | 392 | 7 (29)| 00:00:01 |
| 2 | GENERATE CUBE | | 14 | 392 | 7 (29)| 00:00:01 |
| 3 | SORT GROUP BY | | 14 | 392 | 7 (29)| 00:00:01 |
| 4 | MERGE JOIN | | 14 | 392 | 6 (17)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 7 | SORT JOIN | | 14 | 210 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1175 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
18 rows processed
11:02:52 SCOTT@edw>
You can see the execution plan, and the results are orderly
Partial cube grouping, example
11:06:24 SCOTT@edw> SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY a.dname,CUBE(b.job); DNAME JOB SUM_SAL -------------- --------- ---------- SALES 9400 SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 RESEARCH 10875 RESEARCH CLERK 1900 RESEARCH ANALYST 6000 RESEARCH MANAGER 2975 ACCOUNTING 8750 ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 12 rows selected. Elapsed: 00:00:00.00 11:06:26 SCOTT@edw>
3 subtotal of grouping sets implementation
rollup and cube will generate a variety of standard groups, subtotals, and totals. grouping sets only focuses on subtotals of specified dimensions, and the results of N columns are also n types
For example, grouping sets(a,b,c) is the result of group by a, group by b, and group by c union all
Example
11:06:26 SCOTT@edw> set autotrace on
11:12:33 SCOTT@edw> SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY GROUPING SETS( to_char(b.hiredate,'yyyy'),a.dname,b.job);
HIRE DNAME JOB SUM_SAL
---- -------------- --------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
1987 4100
1980 800
1982 1300
1981 22825
12 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2825031421
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 17 (24)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660D_29B9BB | | | | |
| 3 | MERGE JOIN | | 14 | 504 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 322 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 322 | 3 (0)| 00:00:01 |
| 8 | LOAD AS SELECT | SYS_TEMP_0FD9D660E_29B9BB | | | | |
| 9 | HASH GROUP BY | | 5 | 60 | 3 (34)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_29B9BB | 14 | 168 | 2 (0)| 00:00:01 |
| 11 | LOAD AS SELECT | SYS_TEMP_0FD9D660E_29B9BB | | | | |
| 12 | HASH GROUP BY | | 4 | 56 | 3 (34)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_29B9BB | 14 | 196 | 2 (0)| 00:00:01 |
| 14 | LOAD AS SELECT | SYS_TEMP_0FD9D660E_29B9BB | | | | |
| 15 | HASH GROUP BY | | 1 | 8 | 3 (34)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_29B9BB | 14 | 112 | 2 (0)| 00:00:01 |
| 17 | VIEW | | 5 | 160 | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660E_29B9BB | 5 | 60 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")
filter("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")
Statistics
----------------------------------------------------------
23 recursive calls
33 db block gets
39 consistent gets
4 physical reads
2172 redo size
962 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed
11:12:36 SCOTT@edw>
As you can see from the execution plan, there is no default sorting, which is not related to the order of columns
The same part grouping sets grouping, example
11:12:36 SCOTT@edw> set autotrace off 11:17:03 SCOTT@edw> SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY a.dname,GROUPING SETS(to_char(b.hiredate,'yyyy'),b.job); DNAME HIRE JOB SUM_SAL -------------- ---- --------- ---------- SALES MANAGER 2850 SALES CLERK 950 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 ACCOUNTING CLERK 1300 RESEARCH MANAGER 2975 SALES SALESMAN 5600 RESEARCH ANALYST 6000 RESEARCH CLERK 1900 RESEARCH 1981 5975 SALES 1981 9400 RESEARCH 1987 4100 ACCOUNTING 1981 7450 ACCOUNTING 1982 1300 RESEARCH 1980 800 15 rows selected. Elapsed: 00:00:00.01 11:17:05 SCOTT@edw>
Notice that the meaning has changed a lot at this time
cube and roll up as parameters of grouping sets
grouping sets only provides single column grouping, without the function of total. If you need to provide total, you can use roll up or cube as parameters. For example
11:23:59 SCOTT@edw> SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY GROUPING sets(rollup(a.dname),ROLLUP(b.job));
DNAME JOB SUM_SAL
-------------- --------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
29025
29025
10 rows selected.
Elapsed: 00:00:00.02
11:24:02 SCOTT@edw>
The problem is that two total rows are generated, because the rollup or cube, as the grouping sets parameter, is equivalent to the union all of each rollup or cube operation, which is equivalent to
This is a good understanding of the function
For repeated totals, use distinct to remove them. In addition, there are special functions to use later. Group ID can be used to remove repeated groups (different from distinct)
Roll up and cube can also be mixed as parameters, and other extended functions can also be used, such as partial grouping, composite column grouping, connection grouping, etc
Rollup and cube cannot take grouping sets as parameters, neither can rollup and cube take each other as parameters
4 group column, connect group, reset column group
Combining column grouping and connection grouping are very useful in complex reports. Combined column grouping is used to eliminate unnecessary subtotal and keep total. Connection grouping is operated by Cartesian product of each grouping, and grouping is more and more detailed. For the needs that cannot be met by conventional grouping, we can consider
The combination column treats multiple columns as a whole. The following comparison table can clearly show the differences
The connection grouping is more powerful, allowing multiple roll up, cube and grouping sets operations after group by, so the grouping level is more, the report is more detailed, and complex requirements are realized
In fact, no matter the connection groups of the same type or the connection groups of different types, the final group level category is the product of each extended group level category, and the group level is Cartesian product, such as the group level in rollup(a,b),rollup(c), and finally 3 * 2 = 6
Duplicate column grouping means that duplicate columns are allowed in group by, such as group by roll up (a, (a, b)), group by a, roll up (a, b)
Group columns
Example
14:48:13 SCOTT@edw> SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY rollup(a.dname,(to_char(b.hiredate,'yyyy'),b.job));
DNAME HIRE JOB SUM_SAL
-------------- ---- --------- ----------
SALES 1981 CLERK 950
SALES 1981 MANAGER 2850
SALES 1981 SALESMAN 5600
SALES 9400
RESEARCH 1980 CLERK 800
RESEARCH 1981 ANALYST 3000
RESEARCH 1981 MANAGER 2975
RESEARCH 1987 CLERK 1100
RESEARCH 1987 ANALYST 3000
RESEARCH 10875
ACCOUNTING 1981 MANAGER 2450
ACCOUNTING 1981 PRESIDENT 5000
ACCOUNTING 1982 CLERK 1300
ACCOUNTING 8750
29025
15 rows selected.
Elapsed: 00:00:00.00
14:48:16 SCOTT@edw>
Combining column grouping can achieve the similar effect of partial roll up and partial cube grouping and add the total
But this is also troublesome. For the needs of cube and roll up totals and excluding some subtotals, use the grouping UU ID or grouping function
Both cube and rollup can be converted to corresponding grouping sets
Of course, the reverse is OK, but it doesn't make much sense
Connection grouping
Example
14:48:16 SCOTT@edw> SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY rollup(a.dname,b.job),ROLLUP(to_char(b.hiredate,'yyyy'));
DNAME HIRE JOB SUM_SAL
-------------- ---- --------- ----------
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
RESEARCH CLERK 1900
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975
RESEARCH 10875
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
29025
RESEARCH 1980 CLERK 800
RESEARCH 1980 800
1980 800
SALES 1981 CLERK 950
SALES 1981 MANAGER 2850
SALES 1981 SALESMAN 5600
SALES 1981 9400
RESEARCH 1981 ANALYST 3000
RESEARCH 1981 MANAGER 2975
RESEARCH 1981 5975
ACCOUNTING 1981 MANAGER 2450
ACCOUNTING 1981 PRESIDENT 5000
ACCOUNTING 1981 7450
1981 22825
ACCOUNTING 1982 CLERK 1300
ACCOUNTING 1982 1300
1982 1300
RESEARCH 1987 CLERK 1100
RESEARCH 1987 ANALYST 3000
RESEARCH 1987 4100
1987 4100
34 rows selected.
Elapsed: 00:00:00.01
14:57:57 SCOTT@edw>
Cartesian product equivalent to two rollup s
After understanding, with connection grouping, cube can be transformed with rollup, for example, cube(a,b,c) is equal to rollup(a),rollup(b),rollup(c), but it is generally useless for rollup and grouping sets to be transformed into cube
Connection groups are generally of the same type, while connection groups of different types are not commonly used
Duplicate column grouping
Example
14:57:57 SCOTT@edw> SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY a.dname,ROLLUP(a.dname,b.job); DNAME JOB SUM_SAL -------------- --------- ---------- SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 RESEARCH CLERK 1900 RESEARCH ANALYST 6000 RESEARCH MANAGER 2975 ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 SALES 9400 RESEARCH 10875 ACCOUNTING 8750 SALES 9400 RESEARCH 10875 ACCOUNTING 8750 15 rows selected. Elapsed: 00:00:00.00 15:07:14 SCOTT@edw>
A meaningless example, just to show that grammar allows
5. Three extended grouping functions: grouping, grouping and group ID
Three extended grouping functions: grouping, grouping and group ID play an important role in generating meaningful reports, filtering and sorting results, which are commonly used in complex report queries
Note that the parameters of the grouping and grouping [ID functions cannot be combined columns
grouping function is used to make meaningful reports
Filtering and sorting the results by the grouping ID function
Group ID function removes duplicate lines
grouping function
In the extended group by clause, null means subtotal or total, but what if there is a null value in the data? The grouping function specifically deals with the null problem in extended group by grouping:
It takes only one parameter, and the parameters come from the columns in rollup, cube, and grouping sets. Of course, it can also be in group by instead of the above three clauses, but the result must be 0, which is meaningless
The grouping function returns 1 for the subtotal or total column, otherwise 0. Used to distinguish whether the original data contains null, often used with decode. Of course, you can also determine the grouping level to filter some lines, but it will be annoying. Generally, you can use grouping [ID] instead
Example
15:34:01 SCOTT@edw> SELECT decode(GROUPING(a.dname),1,'All departments',a.dname) dname,decode(grouping(b.mgr),1,'All bosses',b.mgr) mgr,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY ROLLUP(a.dname,b.mgr); DNAME MGR SUM_SAL -------------- ---------------------------------------- ---------- SALES 7698 6550 SALES 7839 2850 SALES All bosses 9400 RESEARCH 7566 6000 RESEARCH 7788 1100 RESEARCH 7839 2975 RESEARCH 7902 800 RESEARCH All bosses 10875 ACCOUNTING 5000 ACCOUNTING 7782 1300 ACCOUNTING 7839 2450 ACCOUNTING All bosses 8750 //All departments, all bosses 29025 13 rows selected. Elapsed: 00:00:00.01 15:34:12 SCOTT@edw>
Grouping ABCD ID function
Used to filter grouping levels and sorting results
Multiple parameters can be accepted. The columns from roll up, cube and grouping sets are calculated from left to right. If they are grouped columns, then 0 is subtotal or total columns, and then they are combined into a binary number called bit vector. The bit vector is converted into base 10, which is the final result. It represents grouping level, such as cube(a,b). Then grouping UU ID (a, b) represents the following
The advantage of grouping ID is that it can calculate multiple columns to get grouping level
Example
15:46:26 SCOTT@edw> SELECT a.dname,b.mgr,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY ROLLUP(a.dname,b.mgr,b.job) HAVING grouping_id(a.dname,b.mgr,b.job) IN (0,7);
DNAME MGR JOB SUM_SAL
-------------- ---------- --------- ----------
SALES 7698 CLERK 950
SALES 7698 SALESMAN 5600
SALES 7839 MANAGER 2850
RESEARCH 7566 ANALYST 6000
RESEARCH 7788 CLERK 1100
RESEARCH 7839 MANAGER 2975
RESEARCH 7902 CLERK 800
ACCOUNTING PRESIDENT 5000
ACCOUNTING 7782 CLERK 1300
ACCOUNTING 7839 MANAGER 2450
29025
11 rows selected.
Elapsed: 00:00:00.00
15:46:29 SCOTT@edw>
Group ID function
Group ID has no parameters, because the extended group by clause allows multiple complex grouping operations. Sometimes, in order to realize complex reports, repeated statistics may occur, and the group ID function can distinguish the repeated grouping results. The first occurrence is 0, and the next occurrence is increased by 1. The occurrence of group ID in select is meaningless, which is usually used to eliminate repeated statistics in the living clause
Example
15:46:29 SCOTT@edw> SELECT a.dname,b.job,SUM(b.sal) sum_sal,group_id() gi FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY GROUPING SETS(ROLLUP(a.dname),ROLLUP(b.job)) HAVING group_id()=0;
DNAME JOB SUM_SAL GI
-------------- --------- ---------- ----------
CLERK 4150 0
SALESMAN 5600 0
PRESIDENT 5000 0
MANAGER 8275 0
ANALYST 6000 0
ACCOUNTING 8750 0
RESEARCH 10875 0
SALES 9400 0
29025 0
9 rows selected.
Elapsed: 00:00:00.01
15:55:55 SCOTT@edw>