Oracle Packet Subtotal, total example (use of grouping sets)

1. First create a table

SQL code [SQL]View PlainCopy

1. Create table TE
2. (
3. ID VARCHAR2 (2),
4. T_code VARCHAR2 (4),
5. T_name VARCHAR2 (4),
6. T_amount INTEGER,
7. T_dept VARCHAR2 (4),
8. T_project VARCHAR2 (4),
9. T_type VARCHAR2 (1)
10. )

2. Enter the following data:

SQL code [SQL]View PlainCopy

2. Insert INTO te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
4. VALUES (' 1 ', ' 1 ', ' 1 ', ' Ten ', ' HQ ', ' n ', ' 0 ');
8. Insert INTO te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
10. VALUES (' 2 ', ' 2 ', ' 2 ', ' n ', ' HQ ', ' + ' , ' 0 ');
14. Insert INTO te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
16. VALUES (' 3 ', ' 3 ', ' 3 ', ', ' 1 ', ' a ', ' 0 ');
20. Insert INTO te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
22. VALUES (' 4 ', ' 4 ', ' 4 ', ' n ', ' 1 ', ' + ', ' 0 ');
26. Insert INTO te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
28. VALUES (' 5 ', ' 5 ', ' 5 ', ', ' 2 ', ' + ', ' 0 ');
32. Insert INTO te (ID, T_code, T_name, T_amount, T_dept, T_project, T_type)
34. VALUES (' 6 ', ' 6 ', ' 6 ', ', ' 2 ', ' + ' , ' 0 ');

3. Query full table View

1. [Java] view plain copy
   1. select * from Te;
   
   
   
   
   
   4. Use t.t_dept, t.t_project for group queries
   
   

SQL code [SQL]View PlainCopy

1. Select T.t_dept, T.t_project, sum (t.t_amount) from te T
2. Group by T.t_dept, T.t_project;

The results are as follows:



5. Use T.t_dept, T.t_project, and t.t_dept to make a small count

SQL code [SQL]View PlainCopy

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

The results are as follows:



6. With T.t_dept, T.t_project, and use t.t_dept to make a small count, and do a total

SQL code [SQL]View PlainCopy

1. Select T.t_dept, T.t_project, sum (t.t_amount) from te T
2. Group by grouping sets ((T.t_dept, T.t_project), t.t_dept, null);

The results are as follows:






7. Using Grouping (field)

SQL code [SQL]View PlainCopy

1. Select Grouping (t.t_dept), t.t_dept, T.t_project, sum (t.t_amount) from te T
2. Group by grouping sets ((T.t_dept, T.t_project), t.t_dept, null);

The results are as follows:

Note: null does not execute correctly during stored procedure need to change to ()
Such as

SQL code [SQL]View PlainCopy

1. Select Grouping (t.t_dept), t.t_dept, T.t_project, T.t_type, sum (t.t_amount) from te T
2. Group by grouping sets ((T.t_dept, T.t_project,t.t_type), (T.t_dept,t.t_project), T.t_dept, ());

Oracle Packet Subtotal, total example (use of grouping sets)