Rollup and Cube

_Who can tell me what this means?_

=ROLLUP= and =CUBE= are OLAP terms. See for example http://en.wikipedia.org/wiki/OLAP_cube

----

I don't know much about it myself, but a quick google on the subject gives
me... http://www.winnetmag.com/SQLServer/Article/ArticleID/5104/5104.html and
http://databases.about.com/library/weekly/aa070101a.htm

Both of these imply that the =CUBE= operator causes new rows to be generated
to give a wildcard value to non-numeric columns and summing the numeric columns which match those wildcards. The potential for generating a huge amount of data with cube is implicit, I think - hence its name. =ROLLUP= appears to be related but removes some of the wildcards; I couldn't determine what from the limited
information in the articles. I could not find, on brief examination any more
definitive reference. Anyone got something more definitive than those articles?  It seems to me that you can do with =SUM= everything you can do with =CUBE=.

----

=CUBE= and =ROLLUP= provide addition subtotal rows.  Let's say you are doing a query

     SELECT x, y, SUM(z) FROM t GROUP BY x, y

Let's also say x and y each have two values.  This query will give you the
sums for all records with x1 y1, x1 y2, x2 y1, and x2 y2.  =ROLLUP= and =CUBE= both provide addition subtotals.  =ROLLUP= adds 3 new sums: for all x1, for all x2, and the grand total.

You can imagine that the =GROUP BY= list is being rolled up, so that it goes from being x, y; to being just x; to being empty. The result of the select for the column that is rolled up becomes NULL.  =CUBE= will do all combinations of
sums in the group by list: sum of all x1, all x2, all y1, all y2, and grand
total.  No idea what that has to do with a cube, though I do sort of picture a hyper-cube in my mind for no good reason.

If you ever add =ROLLUP= and =CUBE=, I also recommend adding the =GROUPING()= function so that you can filter out the additional computations you don't want, or do something like

     SELECT CASE WHEN GROUPING(name)
            THEN 'Total' ELSE name END,
            hours
     FROM timesheets
     GROUP BY name;

I've used the feature plenty doing reports, but then I'm a chronic SQL abuser.