Model Clause - Group values

Christian Balz · Feb 18, 2009 2:31PM

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

Solomon Yakobson · Feb 18, 2009 5:02PM

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

666352 · Feb 18, 2009 3:22PM

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

Frank Kulash · Feb 18, 2009 4:16PM

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

Solomon Yakobson · Feb 18, 2009 5:02PM

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 · Feb 18, 2009 5:14PM

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 · 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.

Aketi Jyuuzou · Feb 18, 2009 6:07PM

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 · Feb 18, 2009 6:46PM

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 · Feb 18, 2009 8:21PM

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 · Feb 18, 2009 8:31PM

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 · Feb 19, 2009 8:03AM

Thank you for great opinion.
I fixed my solution

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

Forum Stats

Quick Links

Categories

Model Clause - Group values

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Model Clause - Group values

Best Answer

Answers