Can someone help me, please? -- I want to do the following steps, and
output strictly in SQL*Plus only:

-1- select count(*) from table1;
-2- select count(*) from table2;

Assume the results are r1 and r2, two numbers, and I want the output to
be like:

Total: r1 + r2
Count1: r1
Count2: r2

Can I do this strictly in SQL*Plus?

Thanks.

On 6 Jun 2005 12:08:18 -0700, "RK" <rekaben@yahoo.com> wrote:

>Can someone help me, please? -- I want to do the following steps, and
>output strictly in SQL*Plus only:
>
>-1- select count(*) from table1;
>-2- select count(*) from table2;
>
>Assume the results are r1 and r2, two numbers, and I want the output to
>be like:
>
>Total: r1 + r2
>Count1: r1
>Count2: r2
>
>Can I do this strictly in SQL*Plus?
>
>Thanks.

Yes


--
Sybrand Bakker, Senior Oracle DBA

Can you show me how you would do it?



Sybrand Bakker wrote:
> On 6 Jun 2005 12:08:18 -0700, "RK" <rekaben@yahoo.com> wrote:
>
> >Can someone help me, please? -- I want to do the following steps, and
> >output strictly in SQL*Plus only:
> >
> >-1- select count(*) from table1;
> >-2- select count(*) from table2;
> >
> >Assume the results are r1 and r2, two numbers, and I want the output to
> >be like:
> >
> >Total: r1 + r2
> >Count1: r1
> >Count2: r2
> >
> >Can I do this strictly in SQL*Plus?
> >
> >Thanks.
>
> Yes
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

RK schrieb:
> Can someone help me, please? -- I want to do the following steps, and
> output strictly in SQL*Plus only:
>
> -1- select count(*) from table1;
> -2- select count(*) from table2;
>
> Assume the results are r1 and r2, two numbers, and I want the output to
> be like:
>
> Total: r1 + r2
> Count1: r1
> Count2: r2
>
> Can I do this strictly in SQL*Plus?
>
> Thanks.
>

There are many ways,
for example
SQL> select sum(cnt),nvl(tname,'total')
2 from (select count(*) cnt,'user_tables' tname from user_tables
3 union all select count(*) cnt,'user_indexes' tname from user_indexes)
4 group by rollup(tname)
5 /

SUM(CNT) NVL(TNAME,'TOTAL')
---------- ------------------------------------
348 user_indexes
344 user_tables
692 total

SQL>

Best regards

Maxim

You can use inline view for this
=====================================
select count(*) row_count from table1
UNION
select count(*) row_count from table2
UNION
select sum(row_count) row_count from
(
select count(*) row_count from table1
UNION
select count(*) row_count from table2
)
=======================================
RK wrote:
> Can someone help me, please? -- I want to do the following steps, and
> output strictly in SQL*Plus only:
>
> -1- select count(*) from table1;
> -2- select count(*) from table2;
>
> Assume the results are r1 and r2, two numbers, and I want the output to
> be like:
>
> Total: r1 + r2
> Count1: r1
> Count2: r2
>
> Can I do this strictly in SQL*Plus?
>
> Thanks.

Sorry, I should have given more conditions.

Please try under such conditions:

table1 and table2 are all very big tables, I doubt the UNION could do
it, and do not bother to think about select in select, please;

only 2 select, I assume one on each table, may be allowed or say,
possibly executed in good time frame;

please output like my format:

Total: the-sum-number
Count1: r1
Count2: r2

I do not know if a variable can take the result from the count, if
someone help me that way, it may be better.

Thanks.







Maxim Demenko wrote:
> RK schrieb:
> > Can someone help me, please? -- I want to do the following steps, and
> > output strictly in SQL*Plus only:
> >
> > -1- select count(*) from table1;
> > -2- select count(*) from table2;
> >
> > Assume the results are r1 and r2, two numbers, and I want the output to
> > be like:
> >
> > Total: r1 + r2
> > Count1: r1
> > Count2: r2
> >
> > Can I do this strictly in SQL*Plus?
> >
> > Thanks.
> >
>
> There are many ways,
> for example
> SQL> select sum(cnt),nvl(tname,'total')
> 2 from (select count(*) cnt,'user_tables' tname from user_tables
> 3 union all select count(*) cnt,'user_indexes' tname from user_indexes)
> 4 group by rollup(tname)
> 5 /
>
> SUM(CNT) NVL(TNAME,'TOTAL')
> ---------- ------------------------------------
> 348 user_indexes
> 344 user_tables
> 692 total
>
> SQL>
>
> Best regards
>
> Maxim

RK schrieb:
> Sorry, I should have given more conditions.
>
> Please try under such conditions:
>
> table1 and table2 are all very big tables, I doubt the UNION could do
> it, and do not bother to think about select in select, please;
>
> only 2 select, I assume one on each table, may be allowed or say,
> possibly executed in good time frame;
>
> please output like my format:
>
> Total: the-sum-number
> Count1: r1
> Count2: r2
>
> I do not know if a variable can take the result from the count, if
> someone help me that way, it may be better.
>
> Thanks.
>
>
>
>
>
>
>
> Maxim Demenko wrote:
>
>>RK schrieb:
>>
>>>Can someone help me, please? -- I want to do the following steps, and
>>>output strictly in SQL*Plus only:
>>>
>>>-1- select count(*) from table1;
>>>-2- select count(*) from table2;
>>>
>>>Assume the results are r1 and r2, two numbers, and I want the output to
>>>be like:
>>>
>>>Total: r1 + r2
>>>Count1: r1
>>>Count2: r2
>>>
>>>Can I do this strictly in SQL*Plus?
>>>
>>>Thanks.
>>>
>>
>>There are many ways,
>>for example
>>SQL> select sum(cnt),nvl(tname,'total')
>> 2 from (select count(*) cnt,'user_tables' tname from user_tables
>> 3 union all select count(*) cnt,'user_indexes' tname from user_indexes)
>> 4 group by rollup(tname)
>> 5 /
>>
>> SUM(CNT) NVL(TNAME,'TOTAL')
>>---------- ------------------------------------
>> 348 user_indexes
>> 344 user_tables
>> 692 total
>>
>>SQL>
>>
>>Best regards
>>
>>Maxim
>
>

I am afraid that you don't clearly understand what you writing about.
As you need count(*) from both tables , both tables need be accessed at
least one time (in your case one time is also fully sufficient ).
If you would explain the statement i posted above ( for simplicity let
operate on the tables, not on the views ), you would see:

SQL> create table user_table as select * from all_tables;

Tabelle wurde angelegt.

SQL> create table user_index as select * from all_indexes;

Tabelle wurde angelegt.

SQL> set autotrace on explain
SQL> select sum(cnt),nvl(tname,'total')
2 from (select count(*) cnt,'user_tables' tname from user_table
3 union all select count(*) cnt,'user_indexes' tname from user_index)
4 group by rollup(tname)
5 /

SUM(CNT) NVL(TNAME,'TOTAL')
---------- ------------------------------------
1184 user_indexes
1050 user_tables
2234 total


Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY ROLLUP)
2 1 VIEW
3 2 UNION-ALL
4 3 SORT (AGGREGATE)
5 4 TABLE ACCESS (FULL) OF 'USER_TABLE'
6 3 SORT (AGGREGATE)
7 6 TABLE ACCESS (FULL) OF 'USER_INDEX'



SQL>

From that you could see, that both tables are accessed only one time.
They are not accessed in parallel. They are accessed one after another.
That way you would it do by selecting 1st count manually, storing it in
the variable, then 2nd one , storing that in a variable and then
outputting in a desired format. Of course that can be put into one
procedure. And this one into a package. And the package can be called
from external call. Or not ( if you prefer to do all within the sqlplus
). But to answer on your question - yes. Result of a select count can be
a variable.
You can start reading here
http://download-west.oracle.com/docs...3.htm#i2699801



Best regards


Maxim

I wrote a reply earlier, but it did not show up in the thread. Now here
is it again:

I have to put some conditions here:

table1 and table2 are all very big tables, therefore not easy to do
UNION or select-in-select;

one select count may take a while, therefore only two select may be
allowed or say finish running in allowable time frame;

please use my format only, that is to output like:

Total: the-sum-number
Count1: r1
Count2: r2

And if I have to tell, I may have more tables (table3, table4 in the
sequence that need to get the total). Can someone help me out of the
trouble?

Thanks.



Maxim Demenko wrote:
> RK schrieb:
> > Can someone help me, please? -- I want to do the following steps, and
> > output strictly in SQL*Plus only:
> >
> > -1- select count(*) from table1;
> > -2- select count(*) from table2;
> >
> > Assume the results are r1 and r2, two numbers, and I want the output to
> > be like:
> >
> > Total: r1 + r2
> > Count1: r1
> > Count2: r2
> >
> > Can I do this strictly in SQL*Plus?
> >
> > Thanks.
> >
>
> There are many ways,
> for example
> SQL> select sum(cnt),nvl(tname,'total')
> 2 from (select count(*) cnt,'user_tables' tname from user_tables
> 3 union all select count(*) cnt,'user_indexes' tname from user_indexes)
> 4 group by rollup(tname)
> 5 /
>
> SUM(CNT) NVL(TNAME,'TOTAL')
> ---------- ------------------------------------
> 348 user_indexes
> 344 user_tables
> 692 total
>
> SQL>
>
> Best regards
>
> Maxim

There are all kinds of ways to do this. Both of the following methods
produce the output that you have requested and only run one select
statement on one table at a time, as you insist upon doing.


SET ECHO OFF FEEDBACK OFF HEADING OFF VERIFY OFF
DEFINE total_count = 0
COLUMN running_count NOPRINT NEW_VALUE total_count
COLUMN c1_count NOPRINT NEW_VALUE count1
COLUMN c2_count NOPRINT NEW_VALUE count2
SELECT COUNT (*) AS c1_count,
&total_count + COUNT (*) AS running_count
FROM table1
/
SELECT COUNT (*) AS c2_count,
&total_count + COUNT (*) AS running_count
FROM table2
/
SELECT 'Total: ' || &total_count
FROM DUAL
/
SELECT 'Count1: ' || &count1
FROM DUAL
/
SELECT 'Count2: ' || &count2
FROM DUAL
/


-- or:


SET SERVEROUTPUT ON
DECLARE
v_count1 INTEGER;
v_count2 INTEGER;
v_total_count INTEGER;
BEGIN
SELECT COUNT (*) INTO v_count1 FROM table1;
SELECT COUNT (*) INTO v_count2 FROM table2;
v_total_count := v_count1 + v_count2;
DBMS_OUTPUT.PUT_LINE ('Total: ' || v_total_count);
DBMS_OUTPUT.PUT_LINE ('Count1: ' || v_count1);
DBMS_OUTPUT.PUT_LINE ('Count2: ' || v_count2);
END;
/

This is the one I like. Thanks. Also thanks to Maxim and other replies.



Barbara Boehmer wrote:
> There are all kinds of ways to do this. Both of the following methods
> produce the output that you have requested and only run one select
> statement on one table at a time, as you insist upon doing.
>
>
> SET ECHO OFF FEEDBACK OFF HEADING OFF VERIFY OFF
> DEFINE total_count = 0
> COLUMN running_count NOPRINT NEW_VALUE total_count
> COLUMN c1_count NOPRINT NEW_VALUE count1
> COLUMN c2_count NOPRINT NEW_VALUE count2
> SELECT COUNT (*) AS c1_count,
> &total_count + COUNT (*) AS running_count
> FROM table1
> /
> SELECT COUNT (*) AS c2_count,
> &total_count + COUNT (*) AS running_count
> FROM table2
> /
> SELECT 'Total: ' || &total_count
> FROM DUAL
> /
> SELECT 'Count1: ' || &count1
> FROM DUAL
> /
> SELECT 'Count2: ' || &count2
> FROM DUAL
> /
>
>
> -- or:
>
>
> SET SERVEROUTPUT ON
> DECLARE
> v_count1 INTEGER;
> v_count2 INTEGER;
> v_total_count INTEGER;
> BEGIN
> SELECT COUNT (*) INTO v_count1 FROM table1;
> SELECT COUNT (*) INTO v_count2 FROM table2;
> v_total_count := v_count1 + v_count2;
> DBMS_OUTPUT.PUT_LINE ('Total: ' || v_total_count);
> DBMS_OUTPUT.PUT_LINE ('Count1: ' || v_count1);
> DBMS_OUTPUT.PUT_LINE ('Count2: ' || v_count2);
> END;
> /