Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Group Rollup with Distinct

30746Jun 12 2003 — edited Jun 12 2003
Would 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 10 2003
Added on Jun 12 2003
2 comments
929 views