Re: Newbie Grouping Sets, Cube Question

From: Daniel <danielroy10junk_at_hotmail.com>
Date: 29 Jan 2006 18:56:55 -0800
Message-ID: <1138589815.156062.226380@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 - 20:56:55 CST