Oracle group summary statistics function grouping

Oracle group summary statistics function grouping

Two days ago, a colleague asked an oracle database to use grouping to complete a statistical report function. This function is pretty cool. The development grouping report can be done simply with an SQL statement.

The meaning of the grouping (columnA) function: if the current row is generated by rollup aggregation, the field value of columnA is 1 or 0.

Metadata:

Data queried through grouping:

SQL:

Select decode (grouping (f_line) + grouping (f_workarea), 1, 'subtotal ', 2, 'Total', f_workarea) f_workarea, decode (grouping (f_line), 1, count (*) | 'string', f_line) f_line, sum (f_pagesnumber) sum_pagesnumbers from t_testcount group by rollup (f_workarea, f_line );

Table creation data:

CREATE TABLE t_testcount   (     "F_ID" NUMBER(10,0) ,   "F_WORKAREA" NVARCHAR2(255) ,   "F_LINE" NVARCHAR2(255) ,   "F_REMARK" NVARCHAR2(255), "F_YEAR" VARCHAR2(20 BYTE),  "F_PAGESNUMBER" NVARCHAR2(255)  );insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (1, 'a', 'a1', null, '2014', '1');insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (2, 'a', 'a2', null, '2013', '2');insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (3, 'a', 'a3', null, '2014', '3');insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (4, 'b', 'b1', null, '2014', '1');