posted 16 years ago
CUBEThis is the most generalized aggregation clause. The general syntax is CUBE ( ). It is used with the GROUP BY only. CUBE creates a subtotal of all possible combinations of the set of column in its argument. Once we compute a CUBE on a set of dimension, we can get answer to all possible aggregation questions on those dimensions.
GROUP BY CUBE( a, b, c) is equivalent to
GROUP BY GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )).
ROLLUP
ROLLUP clause is used with GROUP BY to compute the aggregate at the hierarchy levels of a dimension.
ROLLUP(a, b, c) assumes that the hierarchy is "a" drilling down to "b" drilling down to "c".
ROLLUP (a, b, c) is equivalent to GROUPING SETS ( (a, b, c), (a, b), (a), ( )).
your problem is little tricky because you want to use two aggregate functions (sum & avg)in a single query therefore you may have to go for inner query.
you still didn't answer which database are you using ?
Shailesh
Gravitation cannot be held responsible for people falling in love ~ Albert Einstein