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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
