.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Thursday, November 18, 2004
  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!



 
Comments: Post a Comment

Links to this post:

Create a Link



<< Home
You are a developer and work with Oracle and Microsoft technologies? Have a look!
ATOM
How to:
Use updatable views in Access
Get data in Excel from Oracle 1
Get data in Excel from Oracle 2
Draw the Mandelbrot set using C#
Use the "Grouping Sets" SQl Syntax
Use the "Rollup" SQl Syntax
Use the "Rank over" SQl Syntax

Just another link today
A few Oracle and Microsoft links
Strange and usefull Oracle SQL structure
From the datawarehouse to the chart (part 2)
From the datawarehouse to the chart (part 1)
Using updatable views in Access
Introduction
ARCHIVES
October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / July 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /


Powered by Blogger

mboizeau.free.fr