Hi all,
I need some expert advice on a ROLLUP fine point.
summary_table table has 4 columns:
file_id
primary_site
morphology
primary
I want a rollup by (primary_site,p rimary,morpholo gy) where the totals
appear at the
bottom of the sumamry lines. So I used a CASE assigning a "ZZ..." name
to the totals:
select case grouping(primar y_site)
when 1 then 'ZZ--TOTAL' else primary_site end as primary_site,
case grouping (morphology)
when 1 then 'ZZ--TOTAL' else morphology end as morphology,
case grouping (primary)
when 1 then 'ZZ--TOTAL' else primary end as primary,
count(*) as COUNT from summary_table group by rollup
(primary_site,p rimary,morpholo gy )
order by primary_site,pr imary,morpholog y
This works, but,since I have rows with <nulls> in some columns, the
<null> entries
sort AFTER the "ZZ..." rows, so it looks ugly:
PRIMARY_SITE MORPHOLOGY PRIMARY COUNT
------------------------------------------------------------------
Bladder Carcinoma Primary 1
Bladder Mucinous adenocarcinoma Primary 1
Bladder Transitional cell carcinoma Primary 4
Bladder ZZ--TOTAL Primary 11
Bladder <null> Primary 5
Bladder ZZ--TOTAL ZZ--TOTAL 27
Bladder ZZ--TOTAL <null> 16
Bladder <null> <null> 16
............... ........
I'd like one of 2 things:
(a) Remove the total and subtotals from the output or
(b) Find a way for the "ZZ--TOTAL" lines to appear after the <null>s
so the output would look like this:
PRIMARY_SITE MORPHOLOGY PRIMARY COUNT
------------------------------------------------------------------
Bladder Carcinoma Primary 1
Bladder Mucinous adenocarcinoma Primary 1
Bladder Transitional cell carcinoma Primary 4
Bladder <null> Primary 5
Bladder ZZ--TOTAL Primary 11
Bladder <null> <null> 16
Bladder ZZ--TOTAL <null> 16
Bladder ZZ--TOTAL ZZ--TOTAL 27
............... ........
Any ideas?
Thanks,
Alejandrina