Home » SQL & PL/SQL » SQL & PL/SQL » Multiple groupings in SQL (Oracle 11.2.0.3, Linux)
Multiple groupings in SQL [message #601425] Wed, 20 November 2013 16:12 Go to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Hi All,

I've below 3 tables. Can someone please help me out on writing sql to check multiple values in differet columns and do the grouping.


CREATE TABLE REF_ENTITY_TBL 
 (GROUP_CD NUMBER(2),
  DEPT_CD  NUMBER(2),
  SUB_CD   NUMBER(3),
  PROD_ID  NUMBER(10)
 );
 
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (1,1,29,1);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (1,24,609,2);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (1,29,119,3);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (1,15,537,12);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,20,503,4);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,20,537,5);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,20,918,6);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,22,921,7);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,22,876,8);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (3,50,655,9);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (3,50,676,10);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (3,77,697,11);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (4,98,537,13);

commit;

select * from ref_entity_tbl;

CREATE TABLE FCT_TXN_DATA
  (TRT_ID  NUMBER(10),
   PROD_ID NUMBER(10),
   TXN_AMT NUMBER(10),
   TXN_ID  NUMBER(10)
  );
  
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 1, 10, 1);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 2, 20, 2);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 1, 15, 3);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 10, 15, 10);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 6, 15, 15);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 3, 30, 4);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 3, 50, 5);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 2, 25, 6);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 11, 25, 11);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 1, 20, 7);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 2, 45, 8);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 9, 25, 9);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 10, 15, 12);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 5, 15, 13);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 4, 10, 14);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 12, 20, 15);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 4, 30, 16);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 5, 20, 17);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 5, 20, 17);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 13, 25, 18);

commit;

select * from FCT_TXN_DATA;

CREATE TABLE REF_TRT_MAP_TBL
 (TRT_ID NUMBER(10),
  GROUP_CD VARCHAR2(20),
  DEPT_CD  VARCHAR2(20),
  SUB_CD   VARCHAR2(20)
 );
 
INSERT INTO REF_TRT_MAP_TBL (TRT_ID, GROUP_CD, DEPT_CD, SUB_CD) VALUES (100,'1,3',null,null ); 
INSERT INTO REF_TRT_MAP_TBL (TRT_ID, GROUP_CD, DEPT_CD, SUB_CD) VALUES (200,2,77,null ); 
INSERT INTO REF_TRT_MAP_TBL (TRT_ID, GROUP_CD, DEPT_CD, SUB_CD) VALUES (300,3,24,537 );

COMMIT;



Based on the ref_trt_map_tbl table i need to select data from FCT_TXN_DATA and REF_ENTITY_TBL do the grouping on TRT_ID, GROUP_CD, DEPT_CD and SUB_CD

1) For TRT_ID 100, REF_TRT_MAP_TBL has only GROUP_CD populated so i need to select the data from FCT_TXN_DATA where the group_cd belongs to 1 and 3
2) For TRT_ID 200, REF_TRT_MAP_TBL has GROUP_CD, DEPT_CD populated, so i need to select all the DEPT_CD belongs to GROUP_CD 2
plus DEPT_CD 77
3) For TRT_ID 300, REF_TRT_MAP_TBL has GROUP_CD, DEPT_CD, SUB_CD populated, so i need to select all the DEPT_CD belongs to GROUP_CD 2
plus DEPT_CD 24 and in addition the records belongs to SUB_CD 537. (It could be multiple values in sub_cd field separated by comma)

Output


     TRT_ID GROUP_CD DEPT_CD SUB_CD    TOT_AMT
----------- -------- ------- ------ ----------
        100        1                        45
        100        3                        15
        200        2      20                50
        200               77                25
        300               24                45
        300                     537         60
        300        3                        40



I've written individual queries, but i've thousdand of trt_id's in my table, Can someone please help me out on making this generic.

Query for TRT_ID 300


SQL> select trt_id, case when group_cd = 3 then group_cd else null end as group_cd,
  2                 case when dept_cd = 24 then dept_cd else null end as dept_cd,
  3                 case when sub_cd = 537 then sub_cd else null end as sub_cd
  4                   ,sum(txn_amt) tot_amt
  5    from FCT_TXN_DATA a, REF_ENTITY_TBL b
  6   where a.prod_id = b.prod_id
  7     and (b.group_cd = 3 or b.dept_cd = 24 or b.sub_cd = 537)
  8     and a.trt_id = 300
  9     group by trt_id, case when group_cd = 3 then group_cd else null end,
 10                 case when dept_cd = 24 then dept_cd else null end,
 11                 case when sub_cd = 537 then sub_cd else null end;




For TRT_ID 200


SQL> select trt_id, case when group_cd = 2 then group_cd else null end as group_cd,
  2                 case when dept_cd = 77 then dept_cd else null end as dept_cd,
  3                 null as sub_cd ,
  4                   sum(txn_amt) tot_amt
  5    from FCT_TXN_DATA a, REF_ENTITY_TBL b
  6   where a.prod_id = b.prod_id
  7     and (b.group_cd = 2 or b.dept_cd = 77)
  8     and a.trt_id = 200
  9     group by trt_id, case when group_cd = 2 then group_cd else null end,
 10                 case when dept_cd = 77 then dept_cd else null end;



Appreciate your help!

Thanks
SS
Re: Multiple groupings in SQL [message #601428 is a reply to message #601425] Wed, 20 November 2013 17:27 Go to previous messageGo to next message
manubatham20
Messages: 443
Registered: September 2010
Location: Champaign, IL
Senior Member

sspn2010 wrote on Wed, 20 November 2013 22:12

Based on the ref_trt_map_tbl table i need to select data from FCT_TXN_DATA and REF_ENTITY_TBL do the grouping on TRT_ID, GROUP_CD, DEPT_CD and SUB_CD.


But you never used this table in your queries? Include this table as well, then rewrite your queries.

Regards,
Manu
Re: Multiple groupings in SQL [message #601487 is a reply to message #601428] Thu, 21 November 2013 07:47 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Hi Manu,

Sorry i didn't used ref_trt_map_tbl in my queries. I tried the below one but getting only one row. Did i miss anything?


select c.trt_id, case when c.group_cd is not null then c.group_cd else null end as group_cd, 
               case when c.dept_cd is not null then c.dept_cd else null end as dept_cd,
               case when c.sub_cd is not null then c.sub_cd else null end as sub_cd ,
                 sum(txn_amt) tot_amt 
  from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
 where a.prod_id = b.prod_id
   and a.trt_id = c.trt_id
   and (c.group_cd = b.group_cd or c.dept_cd = b.dept_cd)
   --(b.group_cd = 2 or b.dept_cd = 77)
   and a.trt_id = 200
   group by c.trt_id, case when c.group_cd is not null then c.group_cd else null end, 
               case when c.dept_cd is not null then c.dept_cd else null end,
               case when c.sub_cd is not null then c.sub_cd else null end;

     TRT_ID GROUP_CD             DEPT_CD              SUB_CD                  TOT_AMT
----------- -------------------- -------------------- -------------------- ----------
        200 2                    77                                                75



Appreicate your help!

Thanks
SS
Re: Multiple groupings in SQL [message #601490 is a reply to message #601487] Thu, 21 November 2013 07:54 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
I used UNION ALL and getting results, but i'm not sure how much efficient it will be with the table (FCT_TXN_DATA) which has almost close 1 Billion records. Is there anyway i can avoid union all.


select c.trt_id, c.group_cd,null as dept_cd, null as sub_cd,
                 sum(txn_amt) tot_amt 
  from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
 where a.prod_id = b.prod_id
   and a.trt_id = c.trt_id
   and c.group_cd = b.group_cd
   --(b.group_cd = 2 or b.dept_cd = 77)
   and a.trt_id = 200
   group by c.trt_id, c.group_cd
union all
select c.trt_id, null as group_cd,c.dept_cd as dept_cd, null as sub_cd,
                 sum(txn_amt) tot_amt 
  from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
 where a.prod_id = b.prod_id
   and a.trt_id = c.trt_id
   and c.dept_cd = b.dept_cd
   --(b.group_cd = 2 or b.dept_cd = 77)
   and a.trt_id = 200
   group by c.trt_id, c.dept_cd;

     TRT_ID GROUP_CD             DEPT_CD              SUB_CD    TOT_AMT
----------- -------------------- -------------------- ------ ----------
        200 2                                                        50
        200                      77                                  25


select c.trt_id, c.group_cd,null as dept_cd, null as sub_cd,
                 sum(txn_amt) tot_amt 
  from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
 where a.prod_id = b.prod_id
   and a.trt_id = c.trt_id
   and c.group_cd = b.group_cd
   --(b.group_cd = 2 or b.dept_cd = 77)
   and a.trt_id = 300
   group by c.trt_id, c.group_cd
union all
select c.trt_id, null as group_cd,c.dept_cd as dept_cd, null as sub_cd,
                 sum(txn_amt) tot_amt 
  from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
 where a.prod_id = b.prod_id
   and a.trt_id = c.trt_id
   and c.dept_cd = b.dept_cd
   --(b.group_cd = 2 or b.dept_cd = 77)
   and a.trt_id = 300
   group by c.trt_id, c.dept_cd
union all
select c.trt_id, null as group_cd, null as dept_cd, c.sub_cd as sub_cd,
                 sum(txn_amt) tot_amt 
  from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
 where a.prod_id = b.prod_id
   and a.trt_id = c.trt_id
   and c.sub_cd = b.sub_cd
   --(b.group_cd = 2 or b.dept_cd = 77)
   and a.trt_id = 300
   group by c.trt_id, c.sub_cd ;

     TRT_ID GROUP_CD             DEPT_CD              SUB_CD                  TOT_AMT
----------- -------------------- -------------------- -------------------- ----------
        300 3                                                                      40
        300                      24                                                45
        300                                           537                          60



Thanks
SS
Re: Multiple groupings in SQL [message #601523 is a reply to message #601490] Thu, 21 November 2013 15:54 Go to previous messageGo to next message
manubatham20
Messages: 443
Registered: September 2010
Location: Champaign, IL
Senior Member

I am not getting any time for writing query. But to make it efficient, you need a case in where clause I think, to scan tables once only.

CASE construct with where clause

Regards,
Manu
Re: Multiple groupings in SQL [message #601532 is a reply to message #601523] Thu, 21 November 2013 22:48 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Hi Manu,

Thank you! I'm trying to write query but not getting expected results.


select c.trt_id, case when b.group_cd is not null then b.group_cd else null end as group_cd, 
                 case when b.dept_cd is not null then b.dept_cd else null end as dept_cd, 
                 case when c.sub_cd is not null then b.sub_cd else null end as sub_cd,
                 sum(txn_amt) tot_amt 
  from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
 where a.prod_id = b.prod_id
   and a.trt_id = c.trt_id
   and ( 
         (c.group_cd is not null and b.group_cd in (select * from table(parse_list(c.group_cd))))
         OR
         (c.dept_cd is not null and b.dept_cd = c.dept_cd)
         OR
         (c.sub_cd is not null and b.sub_cd = c.sub_cd)
       )
  group by c.trt_id,case when b.group_cd is not null then b.group_cd else null end, 
                 case when b.dept_cd is not null then b.dept_cd else null end, 
                 case when c.sub_cd is not null then b.sub_cd else null end;

     TRT_ID   GROUP_CD    DEPT_CD     SUB_CD    TOT_AMT
----------- ---------- ---------- ---------- ----------
        100          1          1                    25
        100          1         24                   120
        100          3         50                    15
        200          2         20                    50
        200          3         77                    25
        300          1         15        537         20
        300          1         24        609         45
        300          2         20        537         15
        300          3         50        655         25
        300          3         50        676         15
        300          4         98        537         25




Thanks
SS
Re: Multiple groupings in SQL [message #601598 is a reply to message #601532] Fri, 22 November 2013 12:26 Go to previous messageGo to next message
manubatham20
Messages: 443
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi SS,

I am also doing this for very first time. I think it should be something like below.

It's not complete and not working even, because I don't have parse_list function.

  SELECT c.trt_id,
         c.group_cd,
         c.dept_cd,
         c.sub_cd,
         SUM (txn_amt) tot_amt
    FROM FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
   WHERE     CASE
                WHEN c.group_cd IS NOT NULL AND c.group_cd = b.group_cd THEN 1
                WHEN c.dept_cd IS NOT NULL AND c.dept_cd = b.dept_cd THEN 1
                WHEN c.sub_cd IS NOT NULL AND c.sub_cd = b.sub_cd THEN 1
                ELSE 0
             END = 1
         AND a.prod_id = b.prod_id
         AND a.trt_id = c.trt_id
GROUP BY c.trt_id,
         c.group_cd,
         c.dept_cd,
         c.sub_cd;


And by looking at the parselist function, I will say you are breaking 1st normalization rule.

I will highly recommend, number must be stored as number. Now you are storing them as string and again parsing as number, very serious mistake btw.

Regards,
Manu
Re: Multiple groupings in SQL [message #601600 is a reply to message #601598] Fri, 22 November 2013 13:17 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
i'm using parse_list function to parse the values in group_cd field in ref_trt_map_tbl as we are putting multiple group_cd values separated by comma.

Here is the code for that function


create or replace type split_tbl as table of VARCHAR2(32767);

create or replace FUNCTION parse_list(p_list varchar2, p_del varchar2 := ',')
    RETURN split_tbl
    PIPELINED IS
    l_idx  pls_integer;
    l_list varchar2(32767) := p_list;
  begin
    loop
      l_idx := instr(l_list, p_del);
      if l_idx > 0 then
        pipe row(substr(l_list, 1, l_idx - 1));
        l_list := substr(l_list, l_idx + length(p_del));
      
      else
        pipe row(l_list);
        exit;
      end if;
    end loop;
    return;
  END parse_list;



Thanks
SS
Re: Multiple groupings in SQL [message #601601 is a reply to message #601600] Fri, 22 November 2013 13:31 Go to previous messageGo to next message
manubatham20
Messages: 443
Registered: September 2010
Location: Champaign, IL
Senior Member

OK, here is code, but giving expected result but in one row, you have to correct group by clause now.

  SELECT c.trt_id,
         c.group_cd,
         c.dept_cd,
         c.sub_cd,
         SUM (txn_amt) tot_amt
    FROM FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
   WHERE     CASE
                WHEN c.group_cd IS NOT NULL AND b.group_cd in (select * from table(parse_list(c.group_cd))) THEN 1
                WHEN c.dept_cd IS NOT NULL AND c.dept_cd = b.dept_cd THEN 1
                WHEN c.sub_cd IS NOT NULL AND c.sub_cd = b.sub_cd THEN 1
                ELSE 0
             END = 1
         AND a.prod_id = b.prod_id
         AND a.trt_id = c.trt_id
         and c.trt_id = 300
GROUP BY c.trt_id,
         c.group_cd,
         c.dept_cd,
         c.sub_cd;


Try, and if you can.

Regards,
Manu

[Updated on: Fri, 22 November 2013 13:31]

Report message to a moderator

Re: Multiple groupings in SQL [message #601609 is a reply to message #601601] Fri, 22 November 2013 22:39 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Thanks Manu!
Re: Multiple groupings in SQL [message #601611 is a reply to message #601609] Fri, 22 November 2013 23:04 Go to previous messageGo to next message
manubatham20
Messages: 443
Registered: September 2010
Location: Champaign, IL
Senior Member

Have you found what you expected, paste your final query here.

Manu
Re: Multiple groupings in SQL [message #601632 is a reply to message #601611] Sat, 23 November 2013 08:48 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
No, trying to modify your query to get the expected results, but not working.

Thanks
SS
Re: Multiple groupings in SQL [message #602131 is a reply to message #601632] Fri, 29 November 2013 15:31 Go to previous messageGo to next message
manubatham20
Messages: 443
Registered: September 2010
Location: Champaign, IL
Senior Member

I also tried, but no success.

This is not exactly un-pivoting. I explored other's examples, they are also going with Union All approach.

May be other members can help!!

Regards,
Manu
Re: Multiple groupings in SQL [message #602150 is a reply to message #601425] Sat, 30 November 2013 04:09 Go to previous message
Lalit Kumar B
Messages: 1885
Registered: May 2013
Location: World Wide on the Web
Senior Member
First of all, your table REF_TRT_MAP_TBL is not normalized. You should never keep '1,3' together in the GROUP_CD. It must be two different rows. By doing the number of rows in the expected output will also vary.

Modify the DDL to :
CREATE TABLE ref_trt_map_tbl 
  ( 
     trt_id   NUMBER(10), 
     group_cd NUMBER(2), 
     dept_cd  NUMBER(2), 
     sub_cd   NUMBER(3) 
  ); 

INSERT INTO ref_trt_map_tbl 
            (trt_id, 
             group_cd, 
             dept_cd, 
             sub_cd) 
VALUES      (100, 
             1, 
             NULL, 
             NULL ); 

INSERT INTO ref_trt_map_tbl 
            (trt_id, 
             group_cd, 
             dept_cd, 
             sub_cd) 
VALUES      (100, 
             3, 
             NULL, 
             NULL ); 

INSERT INTO ref_trt_map_tbl 
            (trt_id, 
             group_cd, 
             dept_cd, 
             sub_cd) 
VALUES      (200, 
             2, 
             77, 
             NULL ); 

INSERT INTO ref_trt_map_tbl 
            (trt_id, 
             group_cd, 
             dept_cd, 
             sub_cd) 
VALUES      (300, 
             3, 
             24, 
             537 );
COMMIT;


I went throught the entire thread, however, I couldn't understand your exact requirement. How in your output is the sum of TXN_AMT being calculated?

SQL> SELECT FTD.TRT_ID,
  2         RTM.GROUP_CD,
  3         RTM.DEPT_CD,
  4         RTM.SUB_CD,
  5         SUM(CASE
  6                WHEN RTM.GROUP_CD IS NULL OR RTM.DEPT_CD IS NULL THEN
  7                 NULL
  8                ELSE
  9                 FTD.TXN_AMT
 10             END) TOT_AMT
 11    FROM FCT_TXN_DATA FTD, REF_TRT_MAP_TBL RTM, REF_ENTITY_TBL RET
 12   WHERE FTD.TRT_ID = RTM.TRT_ID
 13     AND FTD.PROD_ID = RET.PROD_ID
 14   GROUP BY FTD.TRT_ID, RTM.GROUP_CD, RTM.DEPT_CD, RTM.SUB_CD
 15  UNION ALL
 16  SELECT FTD.TRT_ID,
 17         RET.GROUP_CD,
 18         RET.DEPT_CD,
 19         RET.SUB_CD,
 20         SUM(FTD.TXN_AMT) TOT_AMT
 21    FROM FCT_TXN_DATA FTD, REF_TRT_MAP_TBL RTM, REF_ENTITY_TBL RET
 22   WHERE FTD.TRT_ID = RTM.TRT_ID
 23     AND FTD.PROD_ID = RET.PROD_ID
 24   GROUP BY FTD.TRT_ID, RET.GROUP_CD, RET.DEPT_CD, RET.SUB_CD
 25   ORDER BY 1, 2;
 
     TRT_ID   GROUP_CD    DEPT_CD     SUB_CD    TOT_AMT
----------- ---------- ---------- ---------- ----------
        100          1                       
        100          1          1         29         50
        100          1         24        609         40
        100          2         20        918         30
        100          3                       
        100          3         50        676         30
        200          1         29        119         80
        200          1         24        609         25
        200          2         77                   180
        200          2         20        537         20
        200          2         20        503         30
        200          3         77        697         25
        300          1         24        609         45
        300          1          1         29         20
        300          1         15        537         20
        300          2         20        503         10
        300          2         20        537         35
        300          3         50        676         15
        300          3         24        537        195
        300          3         50        655         25
        300          4         98        537         25
 
21 rows selected


Previous Topic: Query to PIVOT
Next Topic: Error in reading XML message and inserting data into another DB using DB link
Goto Forum:
  


Current Time: Sat Aug 02 01:52:04 CDT 2014

Total time taken to generate the page: 0.10547 seconds