This discussion is archived
4 Replies Latest reply: Apr 8, 2013 8:06 AM by 879879 RSS

Merge 2 SQL statements.

879879 Newbie
Currently Being Moderated
I would like to merge these 2 statements into 1 sql, only different between 1 and 2 is where clause.
+1.+
select count(*) aa
from table_x
where id = '1';

+2.+
select count(*) bb
from table_x;

Right now, I merge this way.

with temp as (
select count(*) bb
from table_x
+)+
select count(*) aa, temp.bb
from table_x, temp
where id = '1'

Kindly help drop your suggestion if there any better way to merge this,
since the statement above is rather slow.

Thank You!!
Jess
  • 1. Re: Merge 2 SQL statements.
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    one possible solution:
    SELECT SUM (CASE id WHEN '1' THEN 1 END) aa
         , COUNT (*) bb
      FROM table_x;
    I.e.:
    WITH table_x AS 
    (
       SELECT '1' id FROM DUAL UNION ALL
       SELECT '2' id FROM DUAL
    )
    SELECT SUM (CASE id WHEN '1' THEN 1 END) aa
         , COUNT (*) bb
      FROM table_x;
    
    
            AA         BB
    ---------- ----------
             1          2
    1 row selected.
    When you put some code or output please enclose it between two lines starting with {noformat}
    {noformat}
    
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    Regards.
    Al
    
    Edited by: Alberto Faenza on Apr 8, 2013 4:51 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 2. Re: Merge 2 SQL statements.
    BluShadow Guru Moderator
    Currently Being Moderated
    select distinct
           count(*) over () as cnt
          ,sum(case when id = '1' then 1 else 0 end) over () as cnt_id_1
    from table_x
    Edited by: BluShadow on 08-Apr-2013 15:51
    Just realised you don't need the distinct and analytical function part of it. oops
  • 3. Re: Merge 2 SQL statements.
    John Spencer Oracle ACE
    Currently Being Moderated
    select count(case when id = '1' then 1 end) aa, count(*) bb
    from table_x
    Count will only count non-null values of expression. Case will return null when id is not '1'. By the way, are you sure that id should be a string?

    John
  • 4. Re: Merge 2 SQL statements.
    879879 Newbie
    Currently Being Moderated
    Hi Al,

    Thank you for your suggestion.

    I will add
     next time. :)
    
    Jess.                                                                                                                                                                            

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points