Werbung / Advertisements

SQL Aggregation

SQL

Aggregation

Cube

Werbung / Advertisements

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

Werbung / Advertisements

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);
Werbung / Advertisements

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

You may also like...

Werbung / Advertisements