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

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)

Advertisements

Als Amazon-Partner verdiene ich an qualifizierten Verkäufen!
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.

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

Leave a comment

Your email address will not be published. Required fields are marked *