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
--
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).
--
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).
