12 Replies Latest reply on Feb 19, 2009 5:03 PM by Christian Balz

    Model Clause - Group values

    Christian Balz
      Hi everybody,

      Running the query below:
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
      PL/SQL Release 10.2.0.3.0 - Production
      CORE     10.2.0.3.0     Production
      WITH TABLE_A AS (SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL          
                       SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL UNION ALL          
                       SELECT 1 ID, 'PRODUCT_B' PRODUCT FROM DUAL UNION ALL          
                       SELECT 1 ID, 'PRODUCT_D' PRODUCT FROM DUAL UNION ALL
                       SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL
                       SELECT 1 ID, 'PRODUCT_C' PRODUCT FROM DUAL UNION ALL          
                       SELECT 3 ID, 'PRODUCT_Y' PRODUCT FROM DUAL UNION ALL                    
                       SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL                    
                       SELECT 2 ID, 'PRODUCT_1' PRODUCT FROM DUAL UNION ALL                    
                       SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL                    
                       SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL                                     
                       SELECT 3 ID, 'PRODUCT_Z' PRODUCT FROM DUAL UNION ALL                                     
                       SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL)
         SELECT ID, PRODUCT, TT_SUM, TOTAL
           FROM(SELECT ID, PRODUCT, COUNT(*) TT_SUM
                  FROM TABLE_A
                 GROUP BY ID, PRODUCT)
          MODEL 
      DIMENSION BY(ID, PRODUCT)
       MEASURES (TT_SUM, 0 TOTAL)
          RULES (TOTAL[ANY, ANY] = SUM(TT_SUM) OVER (PARTITION BY ID ORDER BY PRODUCT))
          ORDER BY ID, PRODUCT
      I would like to have the following result, with totals:
      ID | PRODUCT   | TT_SUM | TOTAL
      -------------------------------
      1  | PRODUCT_A | 3      |  3
      1  | PRODUCT_B | 1      |  4
      1  | PRODUCT_C | 1      |  5
         | PRODUCT_D | 1      |  6
         | TOTAL     | 6      | 18
      
      2  | PRODUCT_1 | 1      |  1
      2  | PRODUCT_2 | 2      |  3
         | TOTAL     | 3      |  4
      
      3  | PRODUCT_X | 2      |  2
      3  | PRODUCT_Y | 1      |  3
      3  | PRODUCT_Z | 1      |  4
         | TOTAL     | 4      |  9
      How can I achieve this?

      Tks in Advanced,

      Christian Balz
        • 1. Re: Model Clause - Group values
          666352
          SQL> WITH TABLE_A AS (SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL          
            2                   SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL UNION ALL          
            3                   SELECT 1 ID, 'PRODUCT_B' PRODUCT FROM DUAL UNION ALL          
            4                   SELECT 1 ID, 'PRODUCT_D' PRODUCT FROM DUAL UNION ALL
            5                   SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL
            6                   SELECT 1 ID, 'PRODUCT_C' PRODUCT FROM DUAL UNION ALL          
            7                   SELECT 3 ID, 'PRODUCT_Y' PRODUCT FROM DUAL UNION ALL           
            8                   SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL           
            9                   SELECT 2 ID, 'PRODUCT_1' PRODUCT FROM DUAL UNION ALL           
           10                   SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL           
           11                   SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL           
                     
           12                   SELECT 3 ID, 'PRODUCT_Z' PRODUCT FROM DUAL UNION ALL           
                     
           13                   SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL)
           14     SELECT ID1 ID, PRODUCT1 PRODUCT , TT_SUM, TOTAL
           15       FROM (SELECT ID, PRODUCT, COUNT(1) TT_SUM
           16              FROM TABLE_A
           17             GROUP BY ID, PRODUCT)           
           18     MODEL
           19     PARTITION BY (ID id2 )
           20  DIMENSION BY(PRODUCT)
           21   MEASURES (0 TOTAL, TT_SUM, ID ID1, PRODUCT PRODUCT1 )
           22      RULES (TOTAL[ANY] = SUM(TT_SUM) OVER (PARTITION BY ID2 ORDER BY PRODUCT),
           23              ID1[0]=NULL,
           24              PRODUCT1[0]='Total',
           25              TT_SUM[0]= SUM(TT_SUM)[ANY],
           26              TOTAL[0]= SUM(TOTAL)[ANY])
           27      ORDER BY ID2, PRODUCT ; 
          
                  ID PRODUCT       TT_SUM      TOTAL
          ---------- --------- ---------- ----------
                   1 PRODUCT_A          3          3
                   1 PRODUCT_B          1          4
                   1 PRODUCT_C          1          5
                   1 PRODUCT_D          1          6
                     Total              6         18
                   2 PRODUCT_1          1          1
                   2 PRODUCT_2          2          3
                     Total              3          4
                   3 PRODUCT_X          2          2
                   3 PRODUCT_Y          1          3
                   3 PRODUCT_Z          1          4
                     Total              4          9
          
          12 ligne(s) sélectionnée(s).
          
          SQL> 
          Edited by: Salim Chelabi on 2009-02-18 12:27
          1 person found this helpful
          • 2. Re: Model Clause - Group values
            Frank Kulash
            Hi, Christian,

            Thanks for including the data in such a useful form!

            What is the purpose of the TOTAL TOTAL value (18 for id=2, 4 for id=2 and 9 for id-3)? It seems pretty arbitrary.

            If you don't need that value, you can get the results you want by doing a GROUP BY ROLLUP before you get to the MODEL clause:
            SELECT id, NVL (product, 'TOTAL') AS prodct, tt_sum, total
                 FROM(SELECT id, product, COUNT(*) tt_sum
                        FROM table_a
                       GROUP BY id, ROLLUP (product))
                MODEL 
            PARTITION BY (id)
            DIMENSION BY (product)
             MEASURES (tt_sum, 0 total)
                RULES
                (       total [ANY] = SUM (tt_sum) OVER (ORDER BY product)
                ,       total [NULL] = NULL
                )
                ORDER BY id, product;
            Output:
            .       ID PRODCT        TT_SUM      TOTAL
            ---------- --------- ---------- ----------
                     1 PRODUCT_A          3          3
                     1 PRODUCT_B          1          4
                     1 PRODUCT_C          1          5
                     1 PRODUCT_D          1          6
                     1 TOTAL              6
                     2 PRODUCT_1          1          1
                     2 PRODUCT_2          2          3
                     2 TOTAL              3
                     3 PRODUCT_X          2          2
                     3 PRODUCT_Y          1          3
                     3 PRODUCT_Z          1          4
                     3 TOTAL              4
            1 person found this helpful
            • 3. Re: Model Clause - Group values
              Solomon Yakobson
              There is no need to GROUP BY:
              WITH TABLE_A AS (SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL
                               SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL UNION ALL
                               SELECT 1 ID, 'PRODUCT_B' PRODUCT FROM DUAL UNION ALL
                               SELECT 1 ID, 'PRODUCT_D' PRODUCT FROM DUAL UNION ALL
                               SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL
                               SELECT 1 ID, 'PRODUCT_C' PRODUCT FROM DUAL UNION ALL
                               SELECT 3 ID, 'PRODUCT_Y' PRODUCT FROM DUAL UNION ALL
                               SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL
                               SELECT 2 ID, 'PRODUCT_1' PRODUCT FROM DUAL UNION ALL
                               SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL
                               SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL
                               SELECT 3 ID, 'PRODUCT_Z' PRODUCT FROM DUAL UNION ALL
                               SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL
                              )
              SELECT  ID,
                      PRODUCT,
                      TT_SUM,
                      TOTAL
                FROM  TABLE_A
                MODEL
                RETURN UPDATED ROWS
                DIMENSION BY(ID D1,PRODUCT D2,ROW_NUMBER() OVER(PARTITION BY ID,PRODUCT ORDER BY 1) D3)
                MEASURES(ID,PRODUCT,0 TT_SUM, 0 TOTAL)
                RULES
                  UPSERT ALL
                  (
                   TT_SUM[ANY,ANY,1]       = COUNT(TT_SUM)[CV(),CV(),ANY],
                   TOTAL[ANY,ANY,1]        = SUM(TT_SUM)[CV(),D2 <= CV(),ANY],
                   TT_SUM[ANY,NULL,1]   = SUM(TT_SUM)[CV(),ANY,1],
                   TOTAL[ANY,NULL,1]    = SUM(TOTAL)[CV(),ANY,1],
                   PRODUCT[ANY,NULL,1]  = 'TOTAL'
                  )
                ORDER BY D1,
                         D2 NULLS LAST
              /
              
                      ID PRODUCT       TT_SUM      TOTAL
              ---------- --------- ---------- ----------
                       1 PRODUCT_A          3          3
                       1 PRODUCT_B          1          4
                       1 PRODUCT_C          1          5
                       1 PRODUCT_D          1          6
                         TOTAL              6         18
                       2 PRODUCT_1          1          1
                       2 PRODUCT_2          2          3
                         TOTAL              3          4
                       3 PRODUCT_X          2          2
                       3 PRODUCT_Y          1          3
                       3 PRODUCT_Z          1          4
                         TOTAL              4          9
              
              12 rows selected.
              SY.

              Edited by: Solomon Yakobson on Feb 18, 2009 2:07 PM
              • 4. Re: Model Clause - Group values
                Solomon Yakobson
                Hi Salim,

                Your solution does not sort properly. The only reason you got correct sort order is word Total is greater than any word that starts with P. Just add, for example, X as first character of product names:
                SQL> WITH TABLE_A AS (SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL          
                  2                   SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL UNION ALL          
                  3                   SELECT 1 ID, 'XPRODUCT_B' PRODUCT FROM DUAL UNION ALL          
                  4                   SELECT 1 ID, 'XPRODUCT_D' PRODUCT FROM DUAL UNION ALL
                  5                   SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL
                  6                   SELECT 1 ID, 'XPRODUCT_C' PRODUCT FROM DUAL UNION ALL          
                  7                   SELECT 3 ID, 'XPRODUCT_Y' PRODUCT FROM DUAL UNION ALL           
                  8                   SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL           
                  9                   SELECT 2 ID, 'XPRODUCT_1' PRODUCT FROM DUAL UNION ALL           
                 10                   SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL           
                 11                   SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL           
                 12                   SELECT 3 ID, 'XPRODUCT_Z' PRODUCT FROM DUAL UNION ALL           
                 13                   SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL)
                 14     SELECT ID1 ID, PRODUCT1 PRODUCT , TT_SUM, TOTAL
                 15       FROM (SELECT ID, PRODUCT, COUNT(1) TT_SUM
                 16              FROM TABLE_A
                 17             GROUP BY ID, PRODUCT)           
                 18     MODEL
                 19     PARTITION BY (ID id2 )
                 20  DIMENSION BY(PRODUCT)
                 21   MEASURES (0 TOTAL, TT_SUM, ID ID1, PRODUCT PRODUCT1 )
                 22      RULES (TOTAL[ANY] = SUM(TT_SUM) OVER (PARTITION BY ID2 ORDER BY PRODUCT),
                 23              ID1[0]=NULL,
                 24              PRODUCT1[0]='Total',
                 25              TT_SUM[0]= SUM(TT_SUM)[ANY],
                 26              TOTAL[0]= SUM(TOTAL)[ANY])
                 27      ORDER BY ID2, PRODUCT ; 
                
                        ID PRODUCT        TT_SUM      TOTAL
                ---------- ---------- ---------- ----------
                           Total               6         18
                         1 XPRODUCT_A          3          3
                         1 XPRODUCT_B          1          4
                         1 XPRODUCT_C          1          5
                         1 XPRODUCT_D          1          6
                           Total               3          4
                         2 XPRODUCT_1          1          1
                         2 XPRODUCT_2          2          3
                           Total               4          9
                         3 XPRODUCT_X          2          2
                         3 XPRODUCT_Y          1          3
                         3 XPRODUCT_Z          1          4
                
                12 rows selected.
                SY.
                • 5. Re: Model Clause - Group values
                  Rob van Wijk
                  A model clause is not necessary here. Plain grouping and analytic functions is enough:
                  SQL> WITH TABLE_A AS (SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL
                    2                   SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL UNION ALL
                    3                   SELECT 1 ID, 'PRODUCT_B' PRODUCT FROM DUAL UNION ALL
                    4                   SELECT 1 ID, 'PRODUCT_D' PRODUCT FROM DUAL UNION ALL
                    5                   SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL
                    6                   SELECT 1 ID, 'PRODUCT_C' PRODUCT FROM DUAL UNION ALL
                    7                   SELECT 3 ID, 'PRODUCT_Y' PRODUCT FROM DUAL UNION ALL
                    8                   SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL
                    9                   SELECT 2 ID, 'PRODUCT_1' PRODUCT FROM DUAL UNION ALL
                   10                   SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL
                   11                   SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL
                   12                   SELECT 3 ID, 'PRODUCT_Z' PRODUCT FROM DUAL UNION ALL
                   13                   SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL)
                   14  select case when grouping(product) = 0 then id end id
                   15       , nvl(product,'TOTAL') product
                   16       , sum(tt_sum)
                   17       , sum(total) total
                   18    from ( select id
                   19                , product
                   20                , count(*) tt_sum
                   21                , sum(count(*)) over (partition by id order by product) total
                   22             from table_a
                   23            group by id
                   24                , product
                   25         )
                   26   group by id
                   27       , rollup(product)
                   28  /
                  
                          ID PRODUCT   SUM(TT_SUM)      TOTAL
                  ---------- --------- ----------- ----------
                           1 PRODUCT_A           3          3
                           1 PRODUCT_B           1          4
                           1 PRODUCT_C           1          5
                           1 PRODUCT_D           1          6
                             TOTAL               6         18
                           2 PRODUCT_1           1          1
                           2 PRODUCT_2           2          3
                             TOTAL               3          4
                           3 PRODUCT_X           2          2
                           3 PRODUCT_Y           1          3
                           3 PRODUCT_Z           1          4
                             TOTAL               4          9
                  
                  12 rijen zijn geselecteerd.
                  Regards,
                  Rob.
                  1 person found this helpful
                  • 6. Re: Model Clause - Group values
                    Aketi Jyuuzou
                    I have used "model clause" :8}

                    My homepage ;-)
                    http://www.geocities.jp/oraclesqlpuzzle/model-8.html
                    WITH TABLE_A AS 
                    (SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL
                     SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL UNION ALL
                     SELECT 1 ID, 'PRODUCT_B' PRODUCT FROM DUAL UNION ALL
                     SELECT 1 ID, 'PRODUCT_D' PRODUCT FROM DUAL UNION ALL
                     SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL
                     SELECT 1 ID, 'PRODUCT_C' PRODUCT FROM DUAL UNION ALL
                     SELECT 3 ID, 'PRODUCT_Y' PRODUCT FROM DUAL UNION ALL
                     SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL
                     SELECT 2 ID, 'PRODUCT_1' PRODUCT FROM DUAL UNION ALL
                     SELECT 1 ID, 'PRODUCT_A' PRODUCT FROM DUAL UNION ALL
                     SELECT 3 ID, 'PRODUCT_X' PRODUCT FROM DUAL UNION ALL
                     SELECT 3 ID, 'PRODUCT_Z' PRODUCT FROM DUAL UNION ALL
                     SELECT 2 ID, 'PRODUCT_2' PRODUCT FROM DUAL)
                    select ID,PRODUCT,cnt,total
                      from TABLE_A
                    group by ID,PRODUCT
                     model
                    partition by(ID)
                    dimension by(PRODUCT)
                    measures(count(*) as cnt,
                             sum(count(*)) over(partition by ID order by PRODUCT) as total)
                    rules(cnt['TOTAL']   = sum(cnt)  [any],
                          total['TOTAL'] = sum(TOTAL)[any])
                    order by ID,PRODUCT;
                    
                    ID  PRODUCT    CNT  TOTAL
                    --  ---------  ---  -----
                     1  PRODUCT_A    3      3
                     1  PRODUCT_B    1      4
                     1  PRODUCT_C    1      5
                     1  PRODUCT_D    1      6
                     1  TOTAL        6     18
                     2  PRODUCT_1    1      1
                     2  PRODUCT_2    2      3
                     2  TOTAL        3      4
                     3  PRODUCT_X    2      2
                     3  PRODUCT_Y    1      3
                     3  PRODUCT_Z    1      4
                     3  TOTAL        4      9
                    1 person found this helpful
                    • 7. Re: Model Clause - Group values
                      Aketi Jyuuzou
                      If we want resultSet which is sorted,
                      we can use below solution B-)
                      select ID,PRODUCT,cnt,total
                        from TABLE_A
                      group by ID,PRODUCT
                       model
                      partition by(ID)
                      dimension by(PRODUCT)
                      measures(count(*) as cnt,
                               sum(count(*)) over(partition by ID order by PRODUCT) as total,
                               0 as isTotal)
                      rules(cnt['TOTAL']   = sum(cnt)  [any],
                            total['TOTAL'] = sum(TOTAL)[any],
                            isTotal['TOTAL'] = 1)
                      order by ID,isTotal,PRODUCT;
                      • 8. Re: Model Clause - Group values
                        Solomon Yakobson
                        Same issue as with Salim's code. Try product names that start with any letter > T:
                        WITH TABLE_A AS 
                        (SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                         SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_B' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_D' PRODUCT FROM DUAL UNION ALL
                         SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_C' PRODUCT FROM DUAL UNION ALL
                         SELECT 3 ID, 'XPRODUCT_Y' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                         SELECT 2 ID, 'XPRODUCT_1' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                         SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL
                         SELECT 3 ID, 'XPRODUCT_Z' PRODUCT FROM DUAL UNION ALL
                         SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL)
                        select ID,PRODUCT,cnt,total
                          from TABLE_A
                        group by ID,PRODUCT
                         model
                        partition by(ID)
                        dimension by(PRODUCT)
                        measures(count(*) as cnt,
                                 sum(count(*)) over(partition by ID order by PRODUCT) as total)
                        rules(cnt['TOTAL']   = sum(cnt)  [any],
                              total['TOTAL'] = sum(TOTAL)[any])
                        order by ID,PRODUCT;
                        
                                ID PRODUCT           CNT      TOTAL
                        ---------- ---------- ---------- ----------
                                 1 TOTAL               6         18
                                 1 XPRODUCT_A          3          3
                                 1 XPRODUCT_B          1          4
                                 1 XPRODUCT_C          1          5
                                 1 XPRODUCT_D          1          6
                                 2 TOTAL               3          4
                                 2 XPRODUCT_1          1          1
                                 2 XPRODUCT_2          2          3
                                 3 TOTAL               4          9
                                 3 XPRODUCT_X          2          2
                                 3 XPRODUCT_Y          1          3
                        
                                ID PRODUCT           CNT      TOTAL
                        ---------- ---------- ---------- ----------
                                 3 XPRODUCT_Z          1          4
                        
                        12 rows selected.
                        Also, you need to ad ID and PRODUCT as measures to make id=null and product='TOTAL' for summary rows:
                        WITH TABLE_A AS 
                        (SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                         SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_B' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_D' PRODUCT FROM DUAL UNION ALL
                         SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_C' PRODUCT FROM DUAL UNION ALL
                         SELECT 3 ID, 'XPRODUCT_Y' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                         SELECT 2 ID, 'XPRODUCT_1' PRODUCT FROM DUAL UNION ALL
                         SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                         SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL
                         SELECT 3 ID, 'XPRODUCT_Z' PRODUCT FROM DUAL UNION ALL
                         SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL)
                        select ID1,PRODUCT1,cnt,total
                          from TABLE_A
                        group by ID,PRODUCT
                         model
                        partition by(ID)
                        dimension by(PRODUCT)
                        measures(id id1,product product1, count(*) as cnt,
                                 sum(count(*)) over(partition by ID order by PRODUCT) as total)
                        rules(cnt[NULL]      = sum(cnt)  [any],
                              total[NULL]    = sum(TOTAL)[any],
                              product1[null] = 'TOTAL')
                        order by ID,PRODUCT NULLS LAST;
                        
                        
                               ID1 PRODUCT1          CNT      TOTAL
                        ---------- ---------- ---------- ----------
                                 1 XPRODUCT_A          3          3
                                 1 XPRODUCT_B          1          4
                                 1 XPRODUCT_C          1          5
                                 1 XPRODUCT_D          1          6
                                   TOTAL               6         18
                                 2 XPRODUCT_1          1          1
                                 2 XPRODUCT_2          2          3
                                   TOTAL               3          4
                                 3 XPRODUCT_X          2          2
                                 3 XPRODUCT_Y          1          3
                                 3 XPRODUCT_Z          1          4
                        
                               ID1 PRODUCT1          CNT      TOTAL
                        ---------- ---------- ---------- ----------
                                   TOTAL               4          9
                        
                        12 rows selected.
                        SY.
                        • 9. Re: Model Clause - Group values
                          Solomon Yakobson
                          Still, you need to take care of ID. Someting like:
                          SQL> WITH TABLE_A AS 
                            2  (SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                            3   SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL UNION ALL
                            4   SELECT 1 ID, 'XPRODUCT_B' PRODUCT FROM DUAL UNION ALL
                            5   SELECT 1 ID, 'XPRODUCT_D' PRODUCT FROM DUAL UNION ALL
                            6   SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL
                            7   SELECT 1 ID, 'XPRODUCT_C' PRODUCT FROM DUAL UNION ALL
                            8   SELECT 3 ID, 'XPRODUCT_Y' PRODUCT FROM DUAL UNION ALL
                            9   SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                           10   SELECT 2 ID, 'XPRODUCT_1' PRODUCT FROM DUAL UNION ALL
                           11   SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                           12   SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL
                           13   SELECT 3 ID, 'XPRODUCT_Z' PRODUCT FROM DUAL UNION ALL
                           14   SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL)
                           15  select DECODE(isTotal,0,ID1,null) ID,PRODUCT,cnt,total
                           16    from TABLE_A
                           17  group by ID,PRODUCT
                           18   model
                           19  partition by(ID ID1)
                           20  dimension by(PRODUCT)
                           21  measures(count(*) as cnt,
                           22           sum(count(*)) over(partition by ID order by PRODUCT) as total,
                           23           0 as isTotal)
                           24  rules(cnt['TOTAL']   = sum(cnt)  [any],
                           25        total['TOTAL'] = sum(TOTAL)[any],
                           26        isTotal['TOTAL'] = 1)
                           27  order by ID1,isTotal,PRODUCT;
                          
                                  ID PRODUCT           CNT      TOTAL
                          ---------- ---------- ---------- ----------
                                   1 XPRODUCT_A          3          3
                                   1 XPRODUCT_B          1          4
                                   1 XPRODUCT_C          1          5
                                   1 XPRODUCT_D          1          6
                                     TOTAL               6         18
                                   2 XPRODUCT_1          1          1
                                   2 XPRODUCT_2          2          3
                                     TOTAL               3          4
                                   3 XPRODUCT_X          2          2
                                   3 XPRODUCT_Y          1          3
                                   3 XPRODUCT_Z          1          4
                          
                                  ID PRODUCT           CNT      TOTAL
                          ---------- ---------- ---------- ----------
                                     TOTAL               4          9
                          
                          12 rows selected.
                          
                          SQL> 
                          SY.
                          • 10. Re: Model Clause - Group values
                            Aketi Jyuuzou
                            Thank you for great opinion.
                            I fixed my solution :D
                            WITH TABLE_A AS 
                            (SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                             SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL UNION ALL
                             SELECT 1 ID, 'XPRODUCT_B' PRODUCT FROM DUAL UNION ALL
                             SELECT 1 ID, 'XPRODUCT_D' PRODUCT FROM DUAL UNION ALL
                             SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL
                             SELECT 1 ID, 'XPRODUCT_C' PRODUCT FROM DUAL UNION ALL
                             SELECT 3 ID, 'XPRODUCT_Y' PRODUCT FROM DUAL UNION ALL
                             SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                             SELECT 2 ID, 'XPRODUCT_1' PRODUCT FROM DUAL UNION ALL
                             SELECT 1 ID, 'XPRODUCT_A' PRODUCT FROM DUAL UNION ALL
                             SELECT 3 ID, 'XPRODUCT_X' PRODUCT FROM DUAL UNION ALL
                             SELECT 3 ID, 'XPRODUCT_Z' PRODUCT FROM DUAL UNION ALL
                             SELECT 2 ID, 'XPRODUCT_2' PRODUCT FROM DUAL)
                            select decode(isTotal,0,copyID) as ID,PRODUCT,cnt,total
                              from TABLE_A
                            group by ID,PRODUCT
                             model
                            partition by(ID as copyID)
                            dimension by(PRODUCT)
                            measures(count(*) as cnt,
                                     sum(count(*)) over(partition by ID order by PRODUCT) as total,
                                     0 as isTotal)
                            rules(cnt    ['TOTAL'] = sum(cnt)  [any],
                                  total  ['TOTAL'] = sum(TOTAL)[any],
                                  isTotal['TOTAL'] = 1)
                            order by copyID,isTotal,PRODUCT;
                            
                              ID  PRODUCT     CNT  TOTAL
                            ----  ----------  ---  -----
                               1  XPRODUCT_A    3      3
                               1  XPRODUCT_B    1      4
                               1  XPRODUCT_C    1      5
                               1  XPRODUCT_D    1      6
                            null  TOTAL         6     18
                               2  XPRODUCT_1    1      1
                               2  XPRODUCT_2    2      3
                            null  TOTAL         3      4
                               3  XPRODUCT_X    2      2
                               3  XPRODUCT_Y    1      3
                               3  XPRODUCT_Z    1      4
                            null  TOTAL         4      9
                            • 11. Re: Model Clause - Group values
                              666352
                              Hi Solomon,

                              Thank you for your remark.

                              Regards
                              Salim.
                              • 12. Re: Model Clause - Group values
                                Christian Balz
                                Tks guys, it was tough choosing a "best answer" but I'm picking ...

                                Not that the other ones were not right...

                                Best regards