Two days ago colleagues asked an oracle to use grouping to complete a statistical report function, this function is handsome. Developing a grouped report directly a SQL is done.
Grouping (ColumnA) function means: If the current line is generated by rollup, then columnA This field value is 1 otherwise 0
Meta Data:
After querying the data by grouping:
Sql:
Select Decode (Grouping (f_line) +grouping (F_workarea), 1, ' Subtotal ', 2, ' total ', F_workarea) F_workarea,decode (Grouping (f_line) , 1,count (*) | | ' (f_line) f_line,sum (f_pagesnumber) sum_pagesnumbers from t_testcount GROUP by Rollup (F_workarea,f_line);
Build table 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 (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, ' n ', ' 1 '); INSERT into T_testcount (f_id, F_workarea, F_line, F_remark, F_year, F_pagesnumber) VALUES (2, ' a ', ' A2 ', null, ' n ', ' 2 '); Insert int o T_testcount (f_id, F_workarea, F_line, F_remark, F_year, F_pagesnumber) VALUES (3, ' A ', ' A3 ', null, ' n ', ' 3 '); Insert I Nto t_testcount (f_id, F_workarea, F_line, F_remark, F_year, F_pagesnumber) VALUES (4, ' B ', ' B1 ', null, ' 2014 ', ' 1 ');
Oracle Database report summary function grouping