RE: a DIFFERENT sql question
Title: RE: a DIFFERENT sql question SELECT DISTINCT t1.category CAT ,t2.type TYP ,SUM(DISTINCT t1.amount1) OVER(PARTITION BY t1.category) Sum1 ,SUM(t2.amount2) OVER(PARTITION BY t1.category, t2.type) Sum2 FROM t1 ,t2 WHERE t1.mykey1 = t2.mykey1 / CAT TYP SUM1 SUM2 AA x 8 27 AA y 8 6 AA z 8 10 BB y 50 27 HTH Tony APonte Home Shopping Network -Original Message- From: STEVE OLLIG [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 13, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: a DIFFERENT sql question ok - i came up with a solution. but in real life i have a lot of amount1's in t1 so it becomes an ugly brute force looking query. anybody have a more elegant solution? 1 select a.category 2 , (select sum(s.amount1) from t1 s where a.category = s.category) as amount1sum 3 , b.type 4 , sum(b.amount2) 5 from t1 a 6 , t2 b 7 where a.mykey1 = b.mykey1 8 group by 9 a.category 10* , b.type SQL / CATEG AMOUNT1SUM TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 8 y 6 AA 8 z 10 BB 50 y 27 4 rows selected. SQL -Original Message- Sent: Thursday, March 13, 2003 10:49 AM To: Multiple recipients of list ORACLE-L 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
a DIFFERENT sql question
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) 6from 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) 6from 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 BB50 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 BB50 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) 7from 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 TYPECOUNT(*) SUM(DISTINCTA.AMOUNT1) SUM(B.AMOUNT2) - - - -- -- -- AA1 x 2 5 3 AA1 y 1 5 6 AA1 All3 5 9 AA2 x 2 3 24 AA2 z 1 3 10 AA2 All3 3 34 AAAll All6 8 43 BB3 y 2 50 27 BB3 All2 50 27 BBAll All2 50 27 All All
RE: a DIFFERENT sql question
ok - i came up with a solution. but in real life i have a lot of amount1's in t1 so it becomes an ugly brute force looking query. anybody have a more elegant solution? 1 select a.category 2 , (select sum(s.amount1) from t1 s where a.category = s.category) as amount1sum 3 , b.type 4 , sum(b.amount2) 5from t1 a 6 , t2 b 7 where a.mykey1 = b.mykey1 8 group by 9 a.category 10* , b.type SQL / CATEG AMOUNT1SUM TYPE SUM(B.AMOUNT2) - -- - -- AA 8 x 27 AA 8 y 6 AA 8 z 10 BB50 y 27 4 rows selected. SQL -Original Message- Sent: Thursday, March 13, 2003 10:49 AM To: Multiple recipients of list ORACLE-L 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) 6from 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) 6from 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 BB50 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 BB50 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) 7from 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
