Currently, there is no version of PG, which supports the rollup.

However, people look for this analytical function features.

ROLLUP queries result can be achieve using the UNION of Queries.
First Let’s Understand what does rollup do:

If SQL Query has col1,col2,col3,aggregate(col4) then rollup

Processing would be something like this.
1. Show the aggregate of col4 as per the col1,col2,col3
2. Then rollup will do the subtotal and will show the result as per the as aggregate of based on col1,col2
3. Then it will show the aggregate/subtotal as per the col1.
4. And at end Total/Sum

In short, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

In PG, this can be achieve by writing a SubQueries and and UNION those.

So, if the rollup query is something like given below:

select col1,col2,col3,agg(col4) from relation group by rollup(col1,col2,col3)   

Then in PG above can be written as:

select col1, col2,col3 agg(col4) from relation group by col1,col2,col3  
UNION  
select col1,col2,NULL,agg(col4) from relation group by col1,col2  
UNION  
select col1,NULL,NULL,agg(col4) from relation group by col1  
UNION  
select NULL,NULL,NULL, agg(col4) from relation;  

Following is an example:
In Oracle:

select manager_id,job_id,sum(salary) from hr.employees group by rollup(manager_id,job_id);  

In PG:

select manager_id,job_id,sum(salary) from hr.employees group by manager_id,job_id  
UNION  
select manager_id , NULL,sum(salary) from hr.employees group by manager_id  
UNION  
select NULL,NULL,sum(salary) from hr.employees;  

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s