Netezza Rollup Group Aggregates using Grouping sets

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

The Netezza rollup functionality gives aggregation results at multiple grouping levels in a single result set. In this article, we will check Netezza rollup group aggregates using grouping sets.

Netezza Rollup Group Aggregates using Grouping sets

Netezza Rollup Group Aggregates using Grouping sets

You may want to aggregate the records in the tables for each records partition in the group. For examples, consider you want to find out sum of salary of the all employees in the department as a different rows in the query result (row with NULL employee name as highlighted in the image). To achieve this, you have to perform the rollup group aggregation using grouping sets functionality available in Netezza nzsql.

Netezza Rollup Group Aggregates using Grouping sets Syntax

Below is the syntax to use grouping sets in Netezza nzsql:

Select col1,col2,sum(col3) as total
from table
group by grouping sets( (col1,col2),(col3) );

The above query will generate the sum of the col3 for the particular group that you mentioned in query. For example, in above syntax it would be (col1,col2).

Netezza Rollup Group Aggregates using Grouping sets Examples

Below is the example of the Netezza rollup group aggregate using grouping sets functionality:

--Create sample table
create table rollup_test as 
select 1 as deptno , 5 as salary , 'AAA' as empname
union
select 2 as deptno , 4 as salary , 'BBB' as empname
union
select 2 as deptno , 4 as salary , 'CCC' as empname
union
select 1 as deptno , 4 as salary , 'DDD' as empname;

--Rollup query
select * from (
select empname, deptno, sum(salary) as Total 
from rollup_test
group by grouping sets( (empname, deptno), (deptno)  )
) x
order by deptno,Total 
;

Result:

EMPNAME DEPTNO TOTAL
DDD 1 4
AAA 1 5
NULL 1 9
CCC 2 4
BBB 2 4
NULL 2 8

Above query finds the sum of salary of all employees in the department as a different rows in the query result. Highlighted rows are the sum of salary which are grouped by the empname and deptno.

Read:

Tags: