SQL Aggregation

Aggregation

Cube

If you have dimensions, you can use the cube function. Imagine you have the dimensions time, state and product. When you use the cube function, you can get an insight of these values (you get all possible subtotals). Also a partial cube query is possible.

SELECT …
FROM …
WHERE …
GROUP BY CUBE(product, year, state);

SELECT …
FROM …
WHERE …
GROUP BY product, CUBE(year, state);

Grouping

Rollup – SQL Aggregation

The rollup clause adds subtotals and a grand total. It extends the GROUP BY clause. You should use it when using time values or countries. The rollup clause begin by the right column and goes to the left. Look at the example below:

ROLLUP(y, m, day) or ROLLUP(country, state, city)

SELECT ...
FROM ...
WHERE ...
GROUP BY ROLLUP(country, state, city);

Also a partial rollup is possible , when you just need some subtotals.

SELECT …
FROM …
WHERE …
GROUP BY col1, col2, ROLLUP(country, state, city);

You like the blog article about SQL aggregation? Check also the full SQL Guide with lots of useful examples.