Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> a DIFFERENT sql question

a DIFFERENT sql question

From: STEVE OLLIG <sollig_at_lifetouch.com>
Date: Thu, 13 Mar 2003 08:49:19 -0800
Message-ID: <F001.005692A3.20030313084919@fatcity.com>


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
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).
Received on Thu Mar 13 2003 - 10:49:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US