Print counts from multiple tables in one SQL

Question

DB version: 11.1.2.3.0

I am using the below SQL:

SELECT tab_nam, cnt  
FROM  
(  
SELECT 'Tab1' "tab_nam", COUNT(1) "cnt" FROM Tab1  
UNION  
SELECT 'Tab2' "tab_nam", COUNT(1) "cnt" FROM Tab2  
);  

TAB_NAM   CNT  
-------   ------  
Tab1      23  
Tab2      10  

CREATE TABLE Tab1 (id NUMBER, name VARCHAR2(100));
CREATE TABLE Tab2 (id NUMBER, name VARCHAR2(100));

Is there any other way to get the output in the above form? I would like to get the resultset in above format only and not horizontally..

Reason for different SQL: I am not able to print the SQL output to HTML. Format used:

PRO <h3>Table Count</h3>
PRO <table>
PRO <tr>
PRO <th>Table Name</th>
PRO <th>Count</th>
PRO </tr>
SELECT '<tr>'||
'<td class="left">'||"tab_name"||'</td>'||
'<td class="center">'||"cnt"||'</td>'||
'</tr>'
FROM  (
SELECT 'Tab1' "tab_name", COUNT(1) "cnt" FROM Tab1
UNION
 SELECT 'Tab2' "tab_name", COUNT(1) "cnt" FROM Tab2);
PRO </table>

Tia..

What counts do you want? add the table structures for Tab1 and tab2 — Ram
– Ram, Commented Oct 12, 2014 at 5:44
I do not see anything wrong with the current query. What is the problem here? — Antoan Milkov
– Antoan Milkov, Commented Oct 12, 2014 at 5:45
I am printing the SQL output to HTML page. And using the above format gives me "Table Not exists" exception when printing the SQL output to HTML page. Hence looking for a different query. — Sunny
– Sunny, Commented Oct 12, 2014 at 5:48
You don't need the outer query. Just write the UNION query by itself. — Barmar
– Barmar, Commented Oct 12, 2014 at 5:48
Is it possible to use loop to output the result/records one by one to HTML from SQL? — 4 Leave Cover
– 4 Leave Cover, Commented Oct 12, 2014 at 5:57

Lalit Kumar B · Accepted Answer · 2014-10-12 07:29:31Z

3

There are several ways of finding the count of SOME/ALL tables in a single query. For example, let's say I want to get the count of rows from all tables in the schema 'SCOTT'.

1.Using SQL

SQL> column table_name format A15;
SQL> SELECT table_name,
  2    to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '
  3    ||owner
  4    ||'.'
  5    ||table_name)),'/ROWSET/ROW/C')) AS COUNT
  6  FROM all_tables
  7  WHERE owner = 'SCOTT'
  8  /

TABLE_NAME           COUNT
--------------- ----------
DEPT                     4
EMP                     14
BONUS                    0
SALGRADE                 5

SQL>

2.With 100% statistics gathered

SQL> EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 100);

PL/SQL procedure successfully completed.

SQL> column owner format A10;
SQL> select owner, table_name, num_rows, sample_size, last_analyzed from all_tables where owner = 'SCOTT';

OWNER      TABLE_NAME        NUM_ROWS SAMPLE_SIZE LAST_ANAL
---------- --------------- ---------- ----------- ---------
SCOTT      DEPT                     4           4 12-OCT-14
SCOTT      EMP                     14          14 12-OCT-14
SCOTT      BONUS                    0           0 12-OCT-14
SCOTT      SALGRADE                 5           5 12-OCT-14

SQL>

3.Using PL/SQL

SQL> SET serveroutput ON;
SQL> DECLARE
  2    var_count NUMBER;
  3  BEGIN
  4    FOR i IN
  5    (SELECT table_name, owner FROM all_tables WHERE owner = 'SCOTT'
  6    )
  7    LOOP
  8      EXECUTE IMMEDIATE 'select count(*) from ' || i.table_name INTO var_count;
  9      dbms_output.put_line(i.table_name ||' - '|| var_count);
 10    END LOOP;
 11  END;
 12  /
DEPT - 4
EMP - 14
BONUS - 0
SALGRADE - 5

PL/SQL procedure successfully completed.

SQL>

But the SQL approach is simpler, isn't it?

answered Oct 12, 2014 at 7:29

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sunny Over a year ago

Thanks man, SQL apprach helped me print output to HTML with great success.

Lalit Kumar B Over a year ago

Also, note that in recent versions, COUNT(1) and COUNT(*) are same in terms of performance.

Collectives™ on Stack Overflow

Print counts from multiple tables in one SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related