How to merge the following two queries into one?

[zxmgh]

Please let me know how to merge the following two queries into one:

select test1.col2,
sum(test1.col3)
from test1
where test1.col2 is not null
group by test1.col2
union
select test1.col2,
test1.col3
from test1
where test1.col2 is null

[tamade]

It just looks like this:

select test1.col2,
sum(test1.col3)
from test1
group by test1.col2

Don't care about the NULL too much.

[tamade]

Ahh, sorry, I made a mistake if you didn't mean group by the NULL col2 like the un-null col2.

[mike9]

You may replace the union by a "union all".

Why do you want to join the 2 queries?

[zxmgh]

Because I don't want to query test1 table twice if I can do it in one query.

[gandolf989]

Try this query instead.

SELECT NVL ( test1.col2,    'ISNULL', 'NOTNULL' ) col2_null,
       SUM ( test1.col3 )
  FROM test1
 GROUP BY NVL ( test1.col2, 'ISNULL', 'NOTNULL' );

[mike9]

could you please show the explain plan of the query using UNION ALL

EXPLAIN PLAN FOR
select test1.col2,
sum(test1.col3)
from test1
where test1.col2 is not null
group by test1.col2
union all
select test1.col2,
test1.col3
from test1
where test1.col2 is null;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

[tamilselvan]

Please let me know how to merge the following two queries into one:

select test1.col2,
sum(test1.col3)
from test1
where test1.col2 is not null
group by test1.col2
union
select test1.col2,
test1.col3 ---- sum is missing ???
from test1
where test1.col2 is null

Tamil

[mike9]

I dont think that sum is missing. If it should be so there would be no reason to divide the query in two parts.

[tamade]

Maybe you all misunderstood.

I guess Mr zxmgh meant that he just wanted the records with un-null col2 be grouped by col2 and calculate col3 as a sum. He didn't want the records with null col2 be grouped, huh?

If so I don't think those two queries could be combined to one unless there is only one record with null col2 in test1.