Group Rollup with Distinct
30746Jun 12 2003 — edited Jun 12 2003Would anybody explain how GROUP's ROLLUP works with Distinct count ?
I have to write a query grouped on col1 and col2 and need a count of ID for each Gender value(Existing values are 1 ,2, and NULL. )and this ID may repeat more than 1 as this is a child table.
I wrote a query as below
select
col1, -- Varchar2(120)
col2, -- Varchar2(120)
count( distinct Decode(Gender,1, ID)) Male, 1--Male, 2/NULL -- Female
count( distinct Decode(Gender,2,'', ID)) Female, -- This used this because I want to count NULL values as 2
from TABLEA
group by rollup(col1,col2)
order by decode(col1,null,chr(1),col1),
decode(col2,null,chr(1),col2)
-- I used Order clause with decode fuction because I want to display Group Totals first for that group.But normally with ROLLUP, Group totals will be displayed at the end.
I got results OK, but Final Totals for Male(Female) NOT EQUAL to sum of Male(Female) values.
It would be great if anybody could explain how this ROLLUP works with distinct counts