GROUP BY ROLLUP

Analytics

GROUP BY ROLLUP

Hi,

I would need Aggregations on 4 different levels:

The hierarchy looks like this:

ALL
---- Type 1
------- Group 1
--------- Product 1
--------- Product 2
------- Group 2
--------- Product 3
--------- Product 4
---- Type 2
------- Group 3
--------- Product 5
--------- Product 6
------- Group 4
--------- Product 7
--------- Product 8

(1) Product Level:

select ReportingMonth
,Product
,Type
,Group
,count (distinct (case when Product_Subscriber = 1 then customer_id else null end)) as Customers
,sum(Activation) as Activations
,sum(Deactivation) as Deactivations
,sum(Churn) as Churns
from db.table group by 1,2,3,4
;

(2) Group Level:

select ReportingMonth
,Type
,Group
,count (distinct (case when Product_Subscriber = 1 then customer_id else null end)) as Customers
,sum(Activation) as Activations
,sum(Deactivation) as Deactivations
,sum(Churn) as Churns
from db.table group by 1,2,3
;

(3) Type Type Level:

select ReportingMonth
,Type
,count (distinct (case when Product_Subscriber = 1 then customer_id else null end)) as Customers
,sum(Activation) as Activations
,sum(Deactivation) as Deactivations
,sum(Churn) as Churns
from db.table group by 1,2
;

(4) All:

select ReportingMonth
,count (distinct (case when Product_Subscriber = 1 then customer_id else null end)) as Customers
,sum(Activation) as Activations
,sum(Deactivation) as Deactivations
,sum(Churn) as Churns
from db.table group by 1
;

Is it possible to achive this with GROUP BY ROLLUP functionality?

Thanks in advance

5 REPLIES
N/A

Re: GROUP BY ROLLUP

This is exactly what ROLLUP is ment for:

select ReportingMonth
,Product
,Type
,Group
,count (distinct (case when Product_Subscriber = 1 then customer_id else null end)) as Customers
,sum(Activation) as Activations
,sum(Deactivation) as Deactivations
,sum(Churn) as Churns
from db.table group by rollup(1,2,3,4)
;

Higher level columns will be NULLs.
To distinguish between real NULLs and NULLs because of rollup, there's a GROUPING function.

Dieter

Re: GROUP BY ROLLUP

Dieter can you please explain Rollup functionality more clearly
N/A

Re: GROUP BY ROLLUP

You'll find all the info you need in the SQL manuals.

Dieter

Re: GROUP BY ROLLUP

In my case I want to match on different combinations of a set of attributes.I have a set of 10 attributes, and I want to match each record with another on all 10, if no match then on 9 attributes, if no match then match on 8 and so on until 3. If I do a self join and try to write equal to and not equal to for each attribute then its getting too complex. Is there any simple way that anybody can suggest please!

I looked at the rollup function, it does all the combinations and thats what I want but I dont want to sum anything here, I want to find exact matches between records .

N/A

Re: GROUP BY ROLLUP

I t's not clear waht you want, at least for me.

Are you talking about a single table?

Can you please elaborate on your tables, data and rules?

Btw, you don't need to do an aggregation when you use GROUP BY :-)