Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Model Clause - Group values

Christian BalzFeb 18 2009 — edited Nov 3 2010
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
This post has been answered by Solomon Yakobson on Feb 18 2009
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 19 2009
Added on Feb 18 2009
12 comments
4,399 views