SQL Analytical : Rollup A few days ago, I started to introduce analytical SQL functions.
After "group by grouping sets" and "rank over", let's talk about "Rollup".

Let's suppose you have the following table :
create table SALES(
PRODUCT_NAME VARCHAR2(255 BYTE),
DAY NUMBER(2),
MONTH NUMBER(2),
YEAR NUMBER(4),
AMOUNT NUMBER(9)
)
Now, suppose you want to calculate the total amount for one day, the total amount for one month and for one year. You may use the "grouping sets" syntax. This is a powerfull feature which allow you many combinations. But have a look to this one:

select PRODUCT_NAME,DAY ,MONTH ,YEAR, sum(AMOUNT) from sales group by rollup (YEAR,MONTH,PRODUCT_NAME)

This syntax will return the sale's amount for one month and one product then for all the month , this for each month and then the grand total.
Notice that you just need to specify your grouping level. Imagine your final application is something like a querying tool for instance an ASP .NET web form (I should talk about this in another post). You can propose your user a grouping level option button which modify dynamically the rollup field list, easily.

Now, let's suppose your table is not completed , some MONTH fields contains a NULL value. How can you make the difference between the real null value and the null value which is the mark of a group sum.
There is a solution which help to see the difference for a querying tool, the "grouping" function.

select PRODUCT_NAME,DAY ,MONTH ,YEAR, sum(AMOUNT), grouping(YEAR), grouping(MONTH),grouping(PRODUCT_NAME)
from sales
group by rollup (YEAR,MONTH,PRODUCT_NAME)

For instance the "grouping(YEAR)" column return 1 when the line is a sum for one year and the rest of the time. Then your Web form will be able to detect if the line is aggregate and on which level (you may want to change the font or color of the line) . You can also use it with a "decode" function for instance to put the text "Total" instead of the product_name. Let's try this :

select
decode(grouping(PRODUCT_NAME),1,'all product' ,PRODUCT_NAME),
decode(grouping(DAY),1,'all month',DAY),
decode(grouping(month),1,'all year', MONTH) ,
YEAR,
sum(AMOUNT)
from sales group by rollup (YEAR,MONTH,PRODUCT_NAME)

Ok That's all folks , hope this help !Any comments are welcome!



  ¶ 7:13 AM