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;
> /