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!

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

Gianni Ceresa

Because it is in a dashboard prompt, what behavior do you expect?
Your prompt could have various visual appearances but in the end only 2 possible values: why to write a query that will perform an operation to only return a long list of only 2 possible values?
Not even going into the logic of the query, what you are trying to do is a huge waste of resources for something that will probably not work as you expect because you have a different need than what you are trying to do.
With only 2 possible values, your prompt should not be "SQL Result" sourced but defined using "Custom Values" where you enter 'Sold' and 'Not sold' .

1 - 1
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,452 views