On Mon, June 29, 2009 11:36, ddf wrote: > > > > On Jun 29, 12:40 am, Michael Moore <michaeljmo...@gmail.com> wrote: >> imho, the crossjoin solution is less straight forward than a union all >> would >> have been. >> select sum(a) >> from ( >> select count(*) a from tab1 >> union all >> select count(*) a from tab2 >> ) ; >> >> Furthermore , using the union all, you can easily add more tables. >> >> Mike >> >> On Fri, Jun 26, 2009 at 4:06 AM, rob wolfe >> <rob.wo...@oraclegeeks.com>wrote: >> >> >> >> >> >> > aww mayank you took all the fun out of it for the OP :-) >> >> > Mayank wrote: >> > > hi shaunak >> >> > > you can use inline view for thet >> >> > > select (table1.s1+table2.s2) as T_sum >> > > from(Select count(*) as s1 from table1) table1,(select count(*) as >> s2 >> > > from table2) table2) >> >> > > Thsnks & Regards >> > > Mayank >> >> > > On Jun 26, 6:00 am, shaunak <shaunak.adgaon...@gmail.com> wrote: >> >> > >> Guys this is a very basic question but i am not able to find the >> > >> solution. Hope somebody comes up with solution for me. >> >> > >> My requirement is to add the counts of two tables. >> >> > >> select count(*) from table1 ; it returns 20 >> > >> select count(*) from table2; it returns 30 >> >> > >> Now i want to have result in single query as 50 >> >> > >> Can this be done in single query ? how ? can somebody help me >> >> > >> Thanks- Hide quoted text - >> >> - Show quoted text - > > I'm so used to writing convoluted queries that the first query didn't > seem confusing to me at all, however the UNION ALL query is much > easier to modify when one needs to include more tables. In terms of > efficiency they're the same, really, in terms of recursive calls and > consistent gets: > > SQL> select (table1.s1+table2.s2) as T_sum > 2 from (Select count(*) as s1 from table1) table1, > 3 (select count(*) as s2 from table2) table2; > > T_SUM > ---------- > 3750 > > > Execution Plan > ---------------------------------------------------------- > Plan hash value: 1974074909 > > ----------------------------------------------------------------------------------- > | Id | Operation | Name | Rows | Bytes | Cost > (%CPU)| Time | > ----------------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | 1 | 26 | 6 > (0)| 00:00:01 | > | 1 | NESTED LOOPS | | 1 | 26 | 6 > (0)| 00:00:01 | > | 2 | VIEW | | 1 | 13 | 3 > (0)| 00:00:01 | > | 3 | SORT AGGREGATE | | 1 | | > | | > | 4 | INDEX FAST FULL SCAN| TAB1_PK | 2500 | | 3 > (0)| 00:00:01 | > | 5 | VIEW | | 1 | 13 | 3 > (0)| 00:00:01 | > | 6 | SORT AGGREGATE | | 1 | | > | | > | 7 | INDEX FAST FULL SCAN| TAB2_PK | 1250 | | 3 > (0)| 00:00:01 | > ----------------------------------------------------------------------------------- > > > Statistics > ---------------------------------------------------------- > 538 recursive calls > 0 db block gets > 121 consistent gets > 0 physical reads > 0 redo size > 409 bytes sent via SQL*Net to client > 396 bytes received via SQL*Net from client > 2 SQL*Net roundtrips to/from client > 12 sorts (memory) > 0 sorts (disk) > 1 rows processed > > SQL> > SQL> alter system flush shared_pool; > > System altered. > > SQL> > SQL> select sum(a) > 2 from ( > 3 select count(*) a from table1 > 4 union all > 5 select count(*) a from table2 > 6 ) ; > > SUM(A) > ---------- > 3750 > > > Execution Plan > ---------------------------------------------------------- > Plan hash value: 4125582673 > > ------------------------------------------------------------------------------------ > | Id | Operation | Name | Rows | Bytes | Cost > (%CPU)| Time | > ------------------------------------------------------------------------------------ > | 0 | SELECT STATEMENT | | 1 | 13 | 6 > (0)| 00:00:01 | > | 1 | SORT AGGREGATE | | 1 | 13 > | | | > | 2 | VIEW | | 2 | 26 | 6 > (0)| 00:00:01 | > | 3 | UNION-ALL | | | > | | | > | 4 | SORT AGGREGATE | | 1 | > | | | > | 5 | INDEX FAST FULL SCAN| TAB1_PK | 2500 | | 3 > (0)| 00:00:01 | > | 6 | SORT AGGREGATE | | 1 | > | | | > | 7 | INDEX FAST FULL SCAN| TAB2_PK | 1250 | | 3 > (0)| 00:00:01 | > ------------------------------------------------------------------------------------ > > > Statistics > ---------------------------------------------------------- > 538 recursive calls > 0 db block gets > 121 consistent gets > 0 physical reads > 0 redo size > 410 bytes sent via SQL*Net to client > 396 bytes received via SQL*Net from client > 2 SQL*Net roundtrips to/from client > 12 sorts (memory) > 0 sorts (disk) > 1 rows processed > > SQL> > > My two cents (and I'll take change from that if there's any to give). > > > David Fitzjarrell > > > That is why i use union all ... I often have to do something similar to that for 6 or 7 queries (yes, pity me) and it is so much easier to add and drop queries.
Also, I tend to think in sets -- too much math back when I was a physics major I guess, and to me the set operations are more intuitive. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
