Hi All,
I am using GROUP BY ROLLUP to obtain result in the format shown below. However the sum obtained by the query in my Totals Rows at the end is not the correct value. it should be 124. I am not able to understand what might be the reason for this. Please help.
Query:
SELECT decode(grouping(ACCTNUM),'1','Totals', ACCTNUM) "Client",COUNT(DISTINCT Rpt.UserId)
"Total Users Logged In" FROM VW_XXX_SUMMARYREPORTDATA
Rpt WHERE Rpt.LoginDate BETWEEN TO_DATE('01-AUG-2011') and TO_DATE('31-AUG-2011')
GROUP BY ROLLUP (Rpt.ACCTNUM)
HAVING (Rpt.ACCTNUM IS NULL OR Rpt.ACCTNUM = 'Totals') OR Rpt.ACCTNUM IS NOT NULL ;
Result:
1XXX013000 1
1XXX018000 1
1XXX015800 1
9XXX000600 1
9XXX000700 23
9XXX004400 1
9XXX004900 1
9XXX012700 1
9XXX016400 1
9XXX019800 1
9XXX020000 2
9XXX029300 5
9XXX000100 2
9XXX000100 1
9XXX003200 1
9XXX000100 1
9XXX000200 3
9XXX001000 11
9XXX001900 50
9XXX002600 1
9XXX000800 3
9XXX001000 1
9XXX000100 11
Totals 122