Oracle grouping Subtotal, total samples (grouping sets use) ____oracle

1. Create a table first
SQL code [SQL]View Plain copy print? CREATE TABLE TE (ID VARCHAR2 (2), T_code VARCHAR2 (4), T_name VARCHAR2 (4), T_amount INTEGER , T_dept VARCHAR2 (4), T_project VARCHAR2 (4), T_type VARCHAR2 (1))

CREATE TABLE TE
(
  ID        VARCHAR2 (2),
  t_code    VARCHAR2 (4),
  t_name    VARCHAR2 (4),
  T_ AMOUNT  INTEGER,
  t_dept    VARCHAR2 (4),
  T_project VARCHAR2 (4),
  t_type    VARCHAR2 (1)
)

2. Data entry is as follows:
SQL code [SQL] View Plain copy print? insert into te  (id, t_code, t_name, t_amount, t_dept, t_project,  T_type)    values  (' 1 ',  ' 1 ',  ' 1 ', 10,  ' headquarters ',  ',  ' 0 ');      insert into te  (id, t_code, t_name, t_amount, t_dept, t_ Project, t_type)    values  (' 2 ',  ' 2 ',  ' 2 ', 20,  ' headquarters ',  ',  ' 0 ') );      insert into te  (id, t_code, t_name, t_amount, t_ Dept, t_project, t_type)    values  (' 3 ',  ' 3 ',  ' 3 ', 30,  ' points 1 ',  ') ",  ' 0");      insert into te  (id, t_code, t_name, t_ Amount, t_dept, t_project, t_type)    values  (' 4 ',  ' 4 ',  ' 4 ',  40,   ' min 1 ',  ',  ' 0 ');      insert into te  (id,&nbsp T_code, t_name, t_amount, t_dept, t_project, t_type)    values  (' 5 ',   ' 5 ',  ' 5 ', 50,  ' min 2 ',  ',  ' 0 ');      insert into te   (Id, t_code, t_name, t_amount, t_dept, t_project, t_type)    values  (' 6 ',  ' 6 ',  ' 6 ', 60,  ' minutes 2 ',  ',  ' 0 ');  

Insert into Te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
values (' 1 ', ' 1 ', ' 1 ', 10, ' headquarters ', ' 90 ', ' 0 '); C1/>insert into Te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
values (' 2 ', ' 2 ', ' 2 ', 20, ' headquarters ', ' 70 ', ' 0 ');

Insert into Te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
values (' 3 ', ' 3 ', ' 3 ', 30, ' minute 1 ', ' 60 ', ' 0 '); C5/>insert into Te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
values (' 4 ', ' 4 ', ' 4 ', 40, ' minute 1 ', ' 50 ', ' 0 ') ');

Insert into Te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
values (' 5 ', ' 5 ', ' 5 ', 50, ' Minute 2 ', ' 40 ', ' 0 '); C9/>insert into Te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
values (' 6 ', ' 6 ', ' 6 ', 60, ' cent 2 ', ' 30 ', ') 0 ');

3. Query full table View [Java]View Plain copy print? select * from Te;

select * from Te;

4. Use t.t_dept, T.t_project for group query

SQL code [SQL]View Plain copy print? Select T.t_dept, T.t_project, sum (t.t_amount) from Te T Group by T.t_dept, T.t_project;

Select T.t_dept, T.t_project, sum (t.t_amount) from Te T
Group by t.t_dept, T.t_project;

The results are as follows:



5. Use T.t_dept, T.t_project, and use t.t_dept to subtotal

SQL code [SQL]View Plain copy print? Select T.t_dept, T.t_project, sum (t.t_amount) from Te T Group by grouping sets ((T.t_dept, T.t_project), t.t_dept);

Select T.t_dept, T.t_project, sum (t.t_amount) from Te T
Group by grouping sets ((T.t_dept, T.t_project), t.t_dept);

The results are as follows:



6. Use T.t_dept, T.t_project, and use t.t_dept to subtotal and make a total

SQL code [SQL]View Plain copy print? Select T.t_dept, T.t_project, sum (t.t_amount) from Te T Group by grouping sets ((T.t_dept, T.t_project), t.t_dept, NULL) ;

Select T.t_dept, T.t_project, sum (t.t_amount) from Te T
Group by grouping sets ((T.t_dept, T.t_project), T.t_dept, nul L);

The results are as follows:






7. Use Grouping (field)

SQL code [SQL]View Plain copy print? Select Grouping (t.t_dept), t.t_dept, T.t_project, sum (t.t_amount) from Te T Group by grouping sets (T.t_dept, T.T_PROJEC T), t.t_dept, NULL);

Select Grouping (t.t_dept), t.t_dept, T.t_project, sum (t.t_amount) from Te T
Group by grouping sets (T.t_dept, T.t_pro ject), t.t_dept, NULL);

The results are as follows:

Note: null does not execute correctly in stored procedure need to be changed to ()
Such as
SQL code [SQL] view plain copy print? Select Grouping (t.t_dept), t.t_dept, T.t_project, T.t_type, sum (t.t_amount) from Te T Group by grouping sets ((T.T_DEP T, T.t_project,t.t_type), (T.t_dept,t.t_project), T.t_dept, ());

Select Grouping (t.t_dept), t.t_dept, T.t_project, T.t_type, sum (t.t_amount) from Te t