Need ROLLUP expert advice!

[apattin]

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,primary,morphology) 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(primary_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,primary,morphology )
order by primary_site,primary,morphology

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

[Another DB2 DBA]

You're right; the NULL values certainly do complicate the task at hand.
The problem I see is that COALESCE by itself doesn't help much either,
since COALESCE(morphology,'') would result in whitespace that would
still show up after the ZZ-TOTAL row in the sort. However, we can set
NULL values to ZY, which places them just before their respective
totals.

There may be more elegant ways to handle this, but I happen to know
that a common table expression could do the job satisfactorily. The
trick is to use the common table expression to change the NULLs to
sort-friendly values (either ZY or ZZ-TOTAL) and then issue a separate
query that selects from that expression. This gives you a two-step
process in which you can modify and then un-modify the values as
needed.

WITH unsorted (primary_sitepre, morphologypre, primarypre, count) AS (
SELECT CASE GROUPING(primary_site)
WHEN 1 THEN 'ZZ-TOTAL' ELSE primary_site END AS primary_sitepre,
CASE GROUPING (morphology)
WHEN 1 THEN 'ZZ-TOTAL' ELSE COALESCE(morphology,'ZY') END AS
morphologypre,
CASE GROUPING (primary)
WHEN 1 THEN 'ZZ-TOTAL' ELSE COALESCE(primary,'ZY') END AS primarypre,
COUNT(*) AS COUNT
FROM session.summary_table
GROUP BY ROLLUP (primary_site,primary,morphology )
)
SELECT
CASE primary_sitepre WHEN 'ZZ-TOTAL' THEN 'TOTAL' ELSE primary_sitepre
END AS primary_site,
CASE morphologypre WHEN 'ZZ-TOTAL' THEN 'TOTAL' WHEN 'ZY' THEN '' ELSE
morphologypre END AS morphology,
CASE primarypre WHEN 'ZZ-TOTAL' THEN 'TOTAL' WHEN 'ZY' THEN '' ELSE
primarypre END AS primary,
count AS count
FROM unsorted
ORDER BY primary_sitepre,primarypre,morphologypre

The second query allows us to create a second set of colums that are
derived from the first set of values in each row. By doing this, we can
display one set of columns (which look nice) while sorting against the
others (which sort properly).

Good luck,

Fred

apattin wrote:

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,primary,morphology) 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(primary_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,primary,morphology )
order by primary_site,primary,morphology

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

[apattin]

CLEVER!

BTW, I found out how to remove the totals from the rollup, in case
anyone is interested:

select primary_site , primary, morphology,
count(*) as COUNT from morphology_summary group by rollup
(primary_site,primary,morphology )
HAVING GROUPING(primary_site)=0 AND GROUPING(morphology)=0 and
grouping(primary)=0
order by primary_site , primary, morphology

the magic is "HAVING GROUPING(xxx)=0 " only selects the non-total rows.

Thanks!

Alejandrina

[Another DB2 DBA]

If you want to suppress totals from a GROUP BY ROLLUP, perhaps you
should use a different grouping method, such as GROUP BY GROUPING SETS,
or a straight GROUP BY(primary_site, primary, morphology)

Am I missing something?