Multi-level data aggregation

Solution: create table t_20120512_a (id varchar2 (6) primary key, cost number (3), www.2cto.com p2 number (3), p3 number (3 )) /create table t_20120512_ B (id varchar2 (6) primary key, name varchar2 (50), B varchar2 (100)/insert into t_20120512_a values ('20140901', 100, 1 ); insert into t_20120512_a values ('20140901', 101001, 2); insert into values ('20140901', 101002, 3); commit; www.2cto.com insert into t_20120512_ B values ', 'China', null); insert into t_20120512_ B values ('123', 'Physical ', null); insert into t_20120512_ B values ('123', 'electric', null ); insert into t_20120512_ B values ('20140901', 'mechanic ', null); commit; select. id, B. name,. total from (select distinct case when GROUPING (substr (id, 1, 3) = 0 then substr (id, 1, 3) else id end id, sum (cost) total from t_20120512_a group by grouping SETS (id, substr (id, 1, 3) a, t_20120512_ B B where. id = B. id;
Author: Blue Paper