Hi

I need the equivalent of the following query in oracle without the rollup construct which gives the same result set as using the rollup.

SELECT
DISTINCT
decode(a1.a, null, 0, 1) dec1 ,
a1.a, a1.b,a1.c from test_groupby a1
GROUP BY rollup(a1.a, a1.b, a1.c)


thanks

Something like this?

Hi Littlefoot

Thanks for the equivalent query .

Hi

I applied the same method for the following query

SELECT DISTINCT (decode(a1.a, null, 0, 1) + decode(a1.b, null, 0, 1) )dec1 ,
a1.a, a1.b,a1.c from test_groupby a1
GROUP BY rollup(a1.a, a1.b, a1.c)

The below query without rollup throws the error Error: ORA-00979: not a GROUP BY expression

SELECT dec1, a, b, c FROM (
SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, b, c FROM test_groupby GROUP BY a, b, c
UNION
SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, b, NULL FROM test_groupby GROUP BY a, b, NULL
UNION
SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, NULL, NULL FROM test_groupby GROUP BY a, NULL, NULL
UNION
SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, NULL, NULL, NULL FROM test_groupby
)
ORDER BY a, b, c

So, I added the column b in the group by statement of

SELECT dec1, a, b, c FROM (
SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, b, c FROM test_groupby GROUP BY a, b, c
UNION
SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, b, NULL FROM test_groupby GROUP BY a, b, NULL
UNION
SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, a, NULL, NULL FROM test_groupby GROUP BY a,b, NULL, NULL
UNION
SELECT (decode(a, null, 0, 1) + decode(b, null, 0, 1) ) dec1, NULL, NULL, NULL FROM test_groupby
)ORDER BY a, b, c

but the result set of the query is not the same as the query with the rollup construct.

Please explain what is wrong with the query and why the result set varies?.

You should have added

(DECODE(a, NULL, 0, 1) + DECODE(b, NULL, 0, 1))

into the GROUP BY clause instead of only "b".

Why are results different? You mean, a new query has value of 2 in the "dec1" column, while the old query has value 1 in there?

Old: DECODE(a, NULL, 0, 1)
New: (DECODE(a, NULL, 0, 1) + DECODE(b, NULL, 0, 1))

See the difference?