hi,

I am strugling to get following aggregation at multiple levels of a dimension using rollup fuction . Unfortunatly its not working as I am expected.
Following is my sample query...
Code:
SELECT patient,visit,AVG(risk)
FROM (
SELECT 1 patient,2 visit, 50 risk FROM DUAL
UNION ALL
SELECT 1 patient,2 visit, 50 risk FROM DUAL
UNION ALL
SELECT 1 patient,3 visit, 15 risk FROM DUAL
UNION ALL
SELECT 1 patient,3 visit, 15 risk FROM DUAL
UNION ALL
SELECT 1 patient,3 visit, 15 risk FROM DUAL
UNION ALL
SELECT 1 patient,4 visit, 80 risk FROM DUAL
)tt GROUP BY visit WITH ROLLUP
Code:
 Result
patient	visit	avg(risk)
1	2	50
1	3	15
1	4	80
1	\N	37.5
Here I need the query to calculate the average risk at visit level first and then average the result values at patient level.

when I use the above rollup fuction, its directly averaging at patient level, which is not the expected result .

Code:
 Desired Result
patient	visit	avg(risk)
1	2	50
1	3	15
1	4	80
1	\N	48.33

Is there any way to do it in a single layer query?

Thanks
Mahesh