Generating a dataset script with subtotal totals with SQL statements

To generate a dataset with subtotal totals using SQL statements:

Test User: Scott

Test table: Dept,emp

//////////////////////////////////

Retrieve a dataset that needs to be counted

Select Dept.dname,emp.job,sal from Emp,dept

where Emp.deptno=dept.deptno;

//////////////////////////////////

Summary according to department name and position, and for each department

Generate ' subtotal ', and finally generate ' totals '.

Select

Decode (Grouping (dept.dname), 1, ' total: ', dept.dname) dname,

Decode (Grouping (emp.job) +grouping (Dept.dname), 1, ' Subtotal: ', emp.job) job,sum (SAL) sum_sal from Emp,dept where emp.deptno= Dept.deptno GROUP BY rollup (Dept.dname,emp.job);

The results of the operation are as follows:

Sql> Select Dept.dname,emp.job,sal from Emp,d

Dname JOB, SAL.

-------------- --------- ----------

Clerk 800

SALES salesman 1600

SALES salesman 1250

The MANAGER 2975

SALES salesman 1250

SALES MANAGER 2850

ACCOUNTING MANAGER 2450

ANALYST 3000

ACCOUNTING PRESIDENT 5000

SALES salesman 1500

Clerk 1100

Dname JOB, SAL.

-------------- --------- ----------

SALES Clerk 950

ANALYST 3000

ACCOUNTING Clerk 1300

14 rows have been selected.

Sql> Select

2 decode (Grouping (dept.dname), 1, ' total: ', de

3 Decode (Grouping (emp.job) +grouping (dept.d

EPT where Emp.deptno=dept.deptno GROUP by Rol

Dname JOB Sum_sal

-------------- --------- ----------

ACCOUNTING Clerk 1300

ACCOUNTING MANAGER 2450

ACCOUNTING PRESIDENT 5000

Subtotal ACCOUNTING: 8750

ANALYST 6000

Clerk 1900

The MANAGER 2975

Small total: 10875

SALES Clerk 950

SALES MANAGER 2850

SALES salesman 5600

Dname JOB Sum_sal

-------------- --------- ----------

SALES Subtotal: 9400

Total: 29025

13 rows have been selected.

Sql>