Oracle rollup operations-group by group and calculate the total

Oracle rollup operations-group by group, and calculate the total rollup for use with goup by, group by group, and calculate the total. Provides information aggregation (similar to "subtotal"). ROLLUP greatly facilitates rollup operations during data statistics and report generation-by group by rollup () the sum of grouping statistics means that if ROLLUP (A, B, C) is used, group by (A, B, C) is first performed, and then (A, B) perform group by, and then ()
Group by, and then perform the group by operation on the entire table. The following is a simple example of www.2cto.com: 1. Do not use group by to query the salaries of jobs in different departments. select job, deptno, sal from emporder by job, deptno job deptno salanalyst 20 3000.00 ANALYST 20 3000.00 ---> group by ANALYST 20 6000.00 CLERK 10 1300.00 CLERK 20 800.00 CLERK 20 1100.00 CLERK 30 950.00 MANAGER 10 2450.00 MANAGER 20 2975.00 MANAGER 30 2850.00 PRESIDENT 10 5000.00 SALESMAN 30 1250.00 SALESMAN 30 1500.00 SALESMAN 30 1250.00 SALESMAN 30 1600.0 0 2. Use group by grouping select job, deptno, sum (sal) total_sal from emp group by rollup (job, deptno); query the employee table, groups positions in different departments, subtotal and total job deptno TOTAL_SALCLERK 10 1300 CLERK 20 1900 CLERK 30 950 CLERK 4150 ANALYST 20 6000 ANALYST 6000 MANAGER 10 2450 MANAGER 20 2975 MANAGER 30 2850 MANAGER 8275 SALESMAN 30 5600 SALESMAN 5600 PRESIDENT 10 5000 PRESIDENT 5000 --> PRESIDENT position total 29025 ---> total if year does not want to accumulate, select year, month, Area, sum (total_sale) from SaleOrder group by year, rollup (month, area) Grouping usage Grouping is used to determine whether the current Column is a total Column, 1 is yes, select Decode (Grouping (area), 1, 'all region', area) area, Decode (Grouping (month), 1, 'all month', month), sum (money) from SaleOrder Group by RollUp (area, month); -- if it is a total column, then, use Decode to convert it to "all months", 'all region' www.2cto.com rollup, and how to remove the total statistics of the last row: grouping_idselect job, deptno, sum (sal) total_sal from emp group B Y rollup (job, deptno) having grouping_id (job, deptno) <= 1 will the total of the last row be removed from the result of the query. Is the grouping_id field consistent with the field in the rollup bracket? It is an oracle function. How is the number of grouping_id for rollup and cube determined? Vector value, which can be considered as the number of binary values, for example, grouping_id (a, B, c). If Column a is null, 0 is not null and 1 is not empty, the same is true for Column B and column c. The result returns a three-digit number, which is converted to decimal in binary format. For example, all values of a, B, and c are non-null, indicating that the data in this row is a total, that is, 111 www.2cto.com is 7, and three columns are 7. If two columns are 11, they are 3. Grouping is used to determine whether the current Column is a total Column. If it is a total Column, the corresponding Column will be a null value select to_char (to_date (tt. inst_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') as inst_date, nvl (c. username, tt. opr_id) as opr_id, nvl (l. name, d. card_type) as card_type, tt. bat_mchnt_id, to_char (sum (tt. txn_amt)/100, 'fm999999999990. 00 ') as txn_amt, count (*) as count from (select nvl (m. chnshort, 'sporadic card sales ') as bat_mchnt_id, t. inst_date, t. opr_id, t. card_deposit Txn_amt, t. card_no from t_dtl_author t left join t_inf_mchnt m on (t. bat_mchnt_id = m. mchnt_id) where ahth_txn_type = '2013' and t. mchnt_id <> '20140901' AND t. inst_date = '2013 '?: 1) tt left join mnguserinfotb c on (c. operatorcardid = tt. opr_id) left join v_inf_card d on (tt. card_no = d. card_no) left join (select distinct (card_type) as card_type, name, mediatype from t_cardkind_para) l on (d. card_type = l. card_type) group by rollup (tt. inst_date, nvl (c. username, tt. opr_id), nvl (l. name, d. card_type), tt. bat_mchnt_id)