5 Replies Latest reply: May 12, 2013 2:02 PM by Deeds_2001 RSS

    Update Table, Summing distinct column contents.

    Deeds_2001
      Hello Everyone,

      Little update problem: -

      CREATE TABLE: -
       
      CREATE TABLE TestTab10
      (
      GROUPID VARCHAR2(10),
      ITEMS_1 NUMBER(2), 
      ITEMS_2 NUMBER(2),
      ITEMS_3 NUMBER(2),
      TOT_ITEMS VARCHAR(50),
      SEQNO NUMBER(10)
      );
      DATA:-
       
      INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10001','12','13','','','123456');
      INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10001','12','','16','','123457');
      INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10002','','','','','123458');
      INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10003','','13','','','123459');
      INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10003','','13','','','123460');
      INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10003','','13','','','123461');
      INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10004','11','13','','','123462');
      INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10004','','12','','','123463');
      INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10004','','13','14','','123464');
      Select * from TestTab10;
      GROUPID       ITEMS_1    ITEMS_2    ITEMS_3 TOT_ITEMS                                               SEQNO
      ---------- ---------- ---------- ---------- -------------------------------------------------- ----------
      10001              12         13                                                                   123456
      10001              12                    16                                                        123457
      10002                                                                                              123458
      10003                         13                                                                   123459
      10003                         13                                                                   123460
      10003                         13                                                                   123461
      10004              11         13                                                                   123462
      10004                         12                                                                   123463
      10004                         13         14                                                        123464
      Desired results: -
      GROUPID       ITEMS_1    ITEMS_2    ITEMS_3 TOT_ITEMS                                               SEQNO
      ---------- ---------- ---------- ---------- -------------------------------------------------- ----------
      10001              12         13            121316                                                 123456
      10001              12                    16 121316                                                 123457
      10002                                                                                              123458
      10003                         13            13                                                     123459
      10003                         13            13                                                     123460
      10003                         13            13                                                     123461
      10004              11         13            11131214                                               123462
      10004                         12            11131214                                               123463
      10004                         13         14 11131214                                               123464
      I need to update the Tot_items column with the number of distinct Items held within each groupid.

      Any ideas folks?

      Thanks in advance.
        • 1. Re: Update Table, Summing distinct column contents.
          Hoek
          On database version 11.2 you could use LISTAGG:
          SQL> select * from testtab10;
          
          GROUPID       ITEMS_1    ITEMS_2    ITEMS_3 TOT_ITEMS       SEQNO
          ---------- ---------- ---------- ---------- ---------- ----------
          10001              12         13                           123456
          10001              12                    16                123457
          10002                                                      123458
          10003                         13                           123459
          10003                         13                           123460
          10003                         13                           123461
          10004              11         13                           123462
          10004                         12                           123463
          10004                         13         14                123464
          
          9 rows selected.
          
          SQL> merge into testtab10 t
            2  using ( with t1 as ( select distinct 
            3                             items_1 items
            4                      ,      groupid
            5                      from   testtab10
            6                      union all
            7                      select distinct 
            8                             items_2
            9                      ,      groupid
           10                      from   testtab10
           11                      union all
           12                      select distinct 
           13                             items_3
           14                      ,      groupid
           15                      from   testtab10
           16                    )
           17          select distinct 
           18                 groupid
           19          ,      listagg(items, '') within group (order by items) 
           20                                            over (partition by groupid) items
           21          from   t1
           22        ) q
           23  on (q.groupid = t.groupid)
           24  when matched then update set t.tot_items = q.items;
          
          9 rows merged.
          
          SQL> select * from testtab10;
          
          GROUPID       ITEMS_1    ITEMS_2    ITEMS_3 TOT_ITEMS       SEQNO
          ---------- ---------- ---------- ---------- ---------- ----------
          10001              12         13            121316         123456
          10001              12                    16 121316         123457
          10002                                                      123458
          10003                         13            13             123459
          10003                         13            13             123460
          10003                         13            13             123461
          10004              11         13            11121314       123462
          10004                         12            11121314       123463
          10004                         13         14 11121314       123464
          
          9 rows selected.
          Other ways to aggregate strings are listed and explained here:
          http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

          disclaimer: could be improved (no distincts) but it's a classic 'hangover sunday morning' over here
          • 2. Re: Update Table, Summing distinct column contents.
            EdStevens
            Deeds_2001 wrote:
            Hello Everyone,

            Little update problem: -

            CREATE TABLE: -
             
            CREATE TABLE TestTab10
            (
            GROUPID VARCHAR2(10),
            ITEMS_1 NUMBER(2), 
            ITEMS_2 NUMBER(2),
            ITEMS_3 NUMBER(2),
            TOT_ITEMS VARCHAR(50),
            SEQNO NUMBER(10)
            );
            Why are you defining TOT_ITEMS as a VARCHAR? If it is the sum of other things, isn't it a NUMBER?
            And why VARCHAR instead of VARCHAR2?
            And why store it at all? If it is the sum of other numbers that are stored, then you always run the risk of it being wrong. Just calculate it when you need it - don't store it.
            >
            DATA:-
             
            INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10001','12','13','','','123456');
            INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10001','12','','16','','123457');
            INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10002','','','','','123458');
            INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10003','','13','','','123459');
            INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10003','','13','','','123460');
            INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10003','','13','','','123461');
            INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10004','11','13','','','123462');
            INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10004','','12','','','123463');
            INSERT INTO TestTab10("GROUPID","ITEMS_1","ITEMS_2","ITEMS_3","TOT_ITEMS","SEQNO") VALUES ('10004','','13','14','','123464');
            Select * from TestTab10;
            GROUPID       ITEMS_1    ITEMS_2    ITEMS_3 TOT_ITEMS                                               SEQNO
            ---------- ---------- ---------- ---------- -------------------------------------------------- ----------
            10001              12         13                                                                   123456
            10001              12                    16                                                        123457
            10002                                                                                              123458
            10003                         13                                                                   123459
            10003                         13                                                                   123460
            10003                         13                                                                   123461
            10004              11         13                                                                   123462
            10004                         12                                                                   123463
            10004                         13         14                                                        123464
            Desired results: -
            GROUPID       ITEMS_1    ITEMS_2    ITEMS_3 TOT_ITEMS                                               SEQNO
            ---------- ---------- ---------- ---------- -------------------------------------------------- ----------
            10001              12         13            121316                                                 123456
            10001              12                    16 121316                                                 123457
            10002                                                                                              123458
            10003                         13            13                                                     123459
            10003                         13            13                                                     123460
            10003                         13            13                                                     123461
            10004              11         13            11131214                                               123462
            10004                         12            11131214                                               123463
            10004                         13         14 11131214                                               123464
            I need to update the Tot_items column with the number of distinct Items held within each groupid.

            Any ideas folks?

            Thanks in advance.
            • 3. Re: Update Table, Summing distinct column contents.
              Solomon Yakobson
              Hoek wrote:
              On database version 11.2 you could use LISTAGG:
              or simpler:
              merge
                into  testtab10 a
                using (
                       select  rowid rid,
                               listagg(items_1 || ',' || items_2 || ',' || items_3,',')
                                 within group (order by seqno)
                                 over(partition by groupid) items
                           from  testtab10
                      ) b
                on (a.rowid = b.rid)
                when matched
                  then
                    update
                      set a.tot_items = ltrim(
                                              xmlquery(
                                                       'string-join(distinct-values(ora:tokenize($str,",")),",")'
                                                       passing ',' || b.items as "str"
                                                       returning content
                                                      ).getStringVal(),
                                              ','
                                             )
              /
              
              9 rows merged.
              
              select  *
                from  testtab10
              /
              
              9 rows merged.
              
              SQL> select  *
                2    from  testtab10
                3  /
              
              GROUPID       ITEMS_1    ITEMS_2    ITEMS_3 TOT_ITEMS                 SEQNO
              ---------- ---------- ---------- ---------- -------------------- ----------
              10001              12         13            12,13,16                 123456
              10001              12                    16 12,13,16                 123457
              10002                                                                123458
              10003                         13            13                       123459
              10003                         13            13                       123460
              10003                         13            13                       123461
              10004              11         13            11,13,12,14              123462
              10004                         12            11,13,12,14              123463
              10004                         13         14 11,13,12,14              123464
              
              9 rows selected.
              
              SQL>
              SY.
              • 4. Re: Update Table, Summing distinct column contents.
                Solomon Yakobson
                Missed OP wants items ordered:
                merge
                  into  testtab10 a
                  using (
                         with t as (
                                    select  rowid rid,
                                            listagg(items_1 || ',' || items_2 || ',' || items_3,',')
                                              within group (order by seqno)
                                              over(partition by groupid) items
                                        from  testtab10
                                   )
                         select  rid,
                                 xmlquery(
                                          'string-join(for $n in distinct-values(ora:tokenize($str,",")) order by xs:integer($n) return $n,",")'
                                          passing ',' || items as "str"
                                          returning content
                                         ) tot_items
                           from  t
                        ) b
                  on (a.rowid = b.rid)
                  when matched
                    then
                      update
                        set a.tot_items = b.tot_items
                /
                
                9 rows merged.
                
                SQL>  select  *
                  2     from  testtab10
                  3  /
                
                GROUPID       ITEMS_1    ITEMS_2    ITEMS_3 TOT_ITEMS                 SEQNO
                ---------- ---------- ---------- ---------- -------------------- ----------
                10001              12         13            12,13,16                 123456
                10001              12                    16 12,13,16                 123457
                10002                                                                123458
                10003                         13            13                       123459
                10003                         13            13                       123460
                10003                         13            13                       123461
                10004              11         13            11,12,13,14              123462
                10004                         12            11,12,13,14              123463
                10004                         13         14 11,12,13,14              123464
                
                9 rows selected.
                
                SQL>
                SY.
                • 5. Re: Update Table, Summing distinct column contents.
                  Deeds_2001
                  Very Clever Solomon, thank you.