| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
9 a.category 10* , b.type
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
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
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
( 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
10 a.category 11 , a.mykey1 12 , a.amount1 13 , b.type
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
10 a.category 11 -- , a.mykey1 12 -- , a.amount1 13 , b.type
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
11 ( a.category 12 , a.mykey1 13 , b.type 14 ) 15 order by 16 a.category 17 , a.mykey1 18 , b.type
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
sollig_at_lifetouch.com
(952)826-4241
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: STEVE OLLIG INET: sollig_at_lifetouch.com 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: ListGuru_at_fatcity.com (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: STEVE OLLIG INET: sollig_at_lifetouch.com 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: ListGuru_at_fatcity.com (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).Received on Thu Mar 13 2003 - 13:24:02 CST
 |
 |