Autonomous Transactions, Tune SQL Developer, sql default value if no row is returned

SQL Aggregation

Rating: 4.67/5. From 3 votes.
Please wait...

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

Werbung / Advertisements

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)

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

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

Werbung / Advertisements
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.

Rating: 4.67/5. From 3 votes.
Please wait...
Werbung / Advertisements