Re: Newbie Grouping Sets, Cube Question

Daniel,

Thank you so much for the great information. I guess a major component I neglected to mention is that the Levels and Types are very subject to change. Does that through a kink into it?

"Daniel" <danielroy10junk_at_hotmail.com> wrote in message news:1138589815.156062.226380_at_g14g2000cwa.googlegroups.com...
> create table google (type varchar2(30), levl varchar2(30), headcount
> number);
> insert into google values ('Hired', 'Partner', 4);
> .
> .
> .
> select * from google;
>
> TYPE LEVL
> HEADCOUNT
> ------------------------------ ------------------------------
> ----------
> Hired Partner
> 4
> Hired Manager
> 2
> Hired Associate
> 4
> Hired Associate
> 4
> Hired Associate
> 5
> Hired Manager
> 5
> Terminated Manager
> 2
> Terminated Associate
> 2
> Terminated Associate
> 2
> Terminated Manager
> 1
> Begin Headcount Manager
> 12
> Begin Headcount Associate
> 15
> Begin Headcount Associate
> 20
> Begin Headcount Partner
> 30
>
> select a.levl, a.totcount, b.totcount, nvl(c.totcount, 0),
> (nvl(c.totcount, 0) / (a.totcount + b.totcount)) * 100
> from (select levl, sum(headcount) totcount from google where type =
> 'Begin Headcount' group by levl) a,
> (select levl, sum(headcount) totcount from google where type = 'Hired'
> group by levl) b,
> (select levl, sum(headcount) totcount from google where type =
> 'Terminated' group by levl) c
> where a.levl = b.levl and a.levl = c.levl (+);
>
> LEVL TOTCOUNT TOTCOUNT NVL(C.TOTCOUNT,0)
> (NVL(C.TOTCOUNT,0)/(A.TOTCOUNT+B.TOTCOUNT))*100
> ------------------------------ ---------- ---------- -----------------
> -----------------------------------------------
> Manager 12 7 3
> 15.7894737
> Associate 35 13 4
> 8.33333333
> Partner 30 4 0
> 0
>
> Sorry for the format, I copied from SQL*Plus, and the result isn't that
> nice, I'm afraid.
>
> HTH
>
> Daniel
>
Received on Sun Jan 29 2006 - 21:29:43 CST