1

I am working at ROLAP on Oracle and i have this Fact Table : Sales(market_id,item_id,sale), and i have this query :

SELECT market_id,item_id,SUM(sale) FROM Sales
GROUP BY CUBE(market_id,item_id);

Is there another way to get the same results but without using "CUBE" function ?

Sawan
  • 162
  • 1
  • 10

2 Answers2

1

Te below query is an equivalent to a query with CUBE(market_id,item_id) clause (gives the same resultset).
But it will be slower, it will read the table 4 times - CUBE is optimized, it reads the table only once.

SELECT market_id,item_id,SUM(sale) 
FROM Sales
GROUP BY market_id,item_id
UNION ALL
SELECT market_id,NULL,SUM(sale) 
FROM Sales
GROUP BY market_id,NULL
UNION ALL
SELECT NULL,item_id,SUM(sale) FROM Sales
GROUP BY NULL,item_id
UNION ALL
SELECT NULL, NULL,SUM(sale) 
FROM Sales
GROUP BY NULL, NULL
krokodilko
  • 35,300
  • 7
  • 55
  • 79
1

You can also solve it by using grouping_sets From Aggregation Function

and it will give you the same result 

    SELECT market_id,item_id,SUM(sale) FROM Sales
    Group by GROUPING SETS ( (market_id,item_id) , (market_id)(item_id), () );
Iglesk
  • 1,131
  • 1
  • 11
  • 14