since we're having fun with SQL today - here's one that's hurting my brain at the moment. I need to sum columns at 2 different groupings in my resultset. The first select is perfect; the 2nd is where i have trouble; but i know i can do stuff like the 3rd example. How do I get the 2nd one to work?
SQL SCRIPT: drop table t1; drop table t2; create table t1 (mykey1 number(5), category varchar2(5), amount1 number(5)); create table t2 (mykey2 number(5), mykey1 number(5), type varchar2(5), amount2 number(5)); insert into t1 values (1, 'AA', 5); insert into t1 values (2, 'AA', 3); insert into t1 values (3, 'BB', 50); insert into t2 values (1, 1, 'x', 1); insert into t2 values (2, 1, 'x', 2); insert into t2 values (3, 1, 'y', 6); insert into t2 values (4, 2, 'x', 4); insert into t2 values (5, 2, 'z', 10); insert into t2 values (6, 2, 'x', 20); insert into t2 values (7, 3, 'y', 12); insert into t2 values (8, 3, 'y', 15); select a.category , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category , a.mykey1 , a.amount1 , b.type / select a.category -- , a.mykey1 , sum(distinct a.amount1) , b.type , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by a.category -- , a.mykey1 -- , a.amount1 , b.type / select decode(grouping(a.category), 1, 'All', a.category) as category , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job , decode(grouping(b.type), 1, 'All', b.type) as type , count(*) , sum(distinct a.amount1) , sum(b.amount2) from t1 a , t2 b where a.mykey1 = b.mykey1 group by rollup ( a.category , a.mykey1 , b.type ) order by a.category , a.mykey1 , b.type / here's what i get: SQL> select a.category 2 , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6 from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 , a.mykey1 12 , a.amount1 13 , b.type 14 / CATEG MYKEY1 SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) ----- ---------- ---------------------- ----- -------------- AA 1 5 x 3 AA 1 5 y 6 AA 2 3 x 24 AA 2 3 z 10 BB 3 50 y 27 5 rows selected. perfect. but this is the problem query: SQL> select a.category 2 -- , a.mykey1 3 , sum(distinct a.amount1) 4 , b.type 5 , sum(b.amount2) 6 from t1 a 7 , t2 b 8 where a.mykey1 = b.mykey1 9 group by 10 a.category 11 -- , a.mykey1 12 -- , a.amount1 13 , b.type 14 / CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) ----- ---------------------- ----- -------------- AA 8 x 27 AA 5 y 6 AA 3 z 10 BB 50 y 27 4 rows selected. wrong. i want the resultset to look like this: CATEG SUM(DISTINCTA.AMOUNT1) TYPE SUM(B.AMOUNT2) ----- ---------------------- ----- -------------- AA 8 x 27 AA 8 y 6 AA 8 z 10 BB 50 y 27 then this is cool, but not what i want: SQL> select decode(grouping(a.category), 1, 'All', a.category) as category 2 , decode(grouping(a.mykey1), 1, 'All', a.mykey1) as job 3 , decode(grouping(b.type), 1, 'All', b.type) as type 4 , count(*) 5 , sum(distinct a.amount1) 6 , sum(b.amount2) 7 from t1 a 8 , t2 b 9 where a.mykey1 = b.mykey1 10 group by rollup 11 ( a.category 12 , a.mykey1 13 , b.type 14 ) 15 order by 16 a.category 17 , a.mykey1 18 , b.type 19 / CATEG JOB TYPE COUNT(*) SUM(DISTINCTA.AMOUNT1) SUM(B.AMOUNT2) ----- ----- ----- ---------- ---------------------- -------------- AA 1 x 2 5 3 AA 1 y 1 5 6 AA 1 All 3 5 9 AA 2 x 2 3 24 AA 2 z 1 3 10 AA 2 All 3 3 34 AA All All 6 8 43 BB 3 y 2 50 27 BB 3 All 2 50 27 BB All All 2 50 27 All All All 8 58 70 11 rows selected. Steve Ollig [EMAIL PROTECTED] (952)826-4241 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).