4 Replies Latest reply on Apr 8, 2013 3:06 PM by 879879

    Merge 2 SQL statements.

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

      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!!
        • 1. Re: Merge 2 SQL statements.

          one possible solution:
          SELECT SUM (CASE id WHEN '1' THEN 1 END) aa
               , COUNT (*) bb
            FROM table_x;
          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}
          SELECT ...
          Edited by: Alberto Faenza on Apr 8, 2013 4:51 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          • 2. Re: Merge 2 SQL statements.
            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
            1 person found this helpful
            • 3. Re: Merge 2 SQL statements.
              John Spencer
              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?

              1 person found this helpful
              • 4. Re: Merge 2 SQL statements.
                Hi Al,

                Thank you for your suggestion.

                I will add
                 next time. :)