Re: Can I do this strictly in SQL*Plus?

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 7 Jun 2005 17:23:22 -0700
Message-ID: <1118190202.627079.56840@f14g2000cwb.googlegroups.com>

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;
/
Received on Tue Jun 07 2005 - 19:23:22 CDT