Forum Stats

  • 3,741,231 Users
  • 2,248,397 Discussions
  • 7,861,693 Comments

Discussions

Model Clause - Group values

Christian Balz
Christian Balz Member Posts: 462 Silver Badge
edited Nov 3, 2010 5:02AM in SQL & PL/SQL
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

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,515 Black Diamond
    edited Feb 18, 2009 5:08PM Accepted Answer
    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

Answers

  • 666352
    666352 Member Posts: 1,442
    edited Feb 18, 2009 4:36PM
    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
    666352
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,727 Red Diamond
    edited Feb 18, 2009 4:22PM
    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
    Frank Kulash
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,515 Black Diamond
    edited Feb 18, 2009 5:08PM Accepted Answer
    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
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,515 Black Diamond
    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.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    edited Feb 18, 2009 5:40PM
    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.
    Rob van Wijk
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 3, 2010 5:02AM
    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
    Aketi Jyuuzou
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Feb 18, 2009 6:47PM
    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;
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,515 Black Diamond
    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.
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,515 Black Diamond
    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.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
  • 666352
    666352 Member Posts: 1,442
    Hi Solomon,

    Thank you for your remark.

    Regards
    Salim.
  • Christian Balz
    Christian Balz Member Posts: 462 Silver Badge
    Tks guys, it was tough choosing a "best answer" but I'm picking ...

    Not that the other ones were not right...

    Best regards
This discussion has been closed.