Need ROLLUP expert advice!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

apattin - 04 Apr 2006 00:12 GMT

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

Reply to this Message

Another DB2 DBA - 04 Apr 2006 05:47 GMT

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

> Hi all,
>
[quoted text clipped - 62 lines]
>
> Alejandrina

Reply to this Message

apattin - 04 Apr 2006 18:19 GMT

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

Reply to this Message

Another DB2 DBA - 04 Apr 2006 18:58 GMT

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?

Reply to this Message