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