how to implement group by grouping sets

[sameer]

Is there any way to implement group by grouping sets() like in oracle. I am writing queries that were previously implemented in oracle but I am not being able to find this feature in vertica. In oracle we can rewrite queries of the format

Code: Select all

SELECT a, b, SUM( c ) FROM t 
GROUP BY GROUPING SETS ( ( a, b ), ( a ), ( b ), ( ) );

to

Code: Select all

SELECT a, b, SUM( c ) FROM t 
  GROUP BY a, b 
UNION ALL
SELECT a, NULL, SUM( c ) FROM t 
  GROUP BY a 
UNION ALL
SELECT NULL, b, SUM( c ) FROM t 
  GROUP BY b 
UNION ALL
SELECT NULL, NULL, SUM( c ) FROM t;

Is there any way out to directly implement group by grouping sets in vertica avoiding multiple table scan...?? like in above example..please help

[bwu@apptio.com]

Not that I know of - but if you had to implement something like that and you are concerned with multiple table scans, one way to mitigate that is to do a group by on "a,b" first, put the result in a tmp table then the subsequent scans can be performed on that tmp table directly. If the cardinality game works out, you may save some time in the end,

Bing

[id10t]

Hi!

[DELETED]

[NorbertKrupa]

Please provide a sample data set, and the desired result. This will help us in identifying the best approach to your problem.