Count(*) Multiple Tables

Question

I was browsing for a solution to getting counts from multiple tables and I came across the following answer:

SELECT COUNT(*),(SELECT COUNT(*) FROM table2) FROM table1

It works great, however I can't seem to get it to work for more than just 2 tables. My current code is as follows:

SELECT COUNT(*), 
(SELECT COUNT(*) FROM TABLE1),
(SELECT COUNT(*) FROM TABLE2),
(SELECT COUNT(*) FROM TABLE3),
(SELECT COUNT(*) FROM TABLE4),
(SELECT COUNT(*) FROM TABLE5),
(SELECT COUNT(*) FROM TABLE6),
(SELECT COUNT(*) FROM TABLE7),
(SELECT COUNT(*) FROM TABLE8),
(SELECT COUNT(*) FROM TABLE9),
(SELECT COUNT(*) FROM TABLE10),
(SELECT COUNT(*) FROM TABLE11),
(SELECT COUNT(*) FROM TABLE12),
(SELECT COUNT(*) FROM TABLE13),
(SELECT COUNT(*) FROM TABLE14),
(SELECT COUNT(*) FROM TABLE15),
(SELECT COUNT(*) FROM TABLE16),
(SELECT COUNT(*) FROM TABLE17),
(SELECT COUNT(*) FROM TABLE18)
FROM TABLE19

However, it only counts TABLE1 and TABLE19. I need to count all tables (TABLE1-18) as well as TABLE19 (hopefully using a structure to similar to the first example).

i think you are solving the wrong problem here .. why exactly you need to do this query ? — tereško, Nov 09 '11 at 20:43
There has to be a more efficient way to accomplish this task. I'll +1 @MarkByers question. — Rob Paller, Nov 09 '11 at 20:43

score 2 · Answer 1 · answered Nov 09 '11 at 20:34

Use aliases so that the columns have unique names:

SELECT 
    (SELECT COUNT(*) FROM TABLE1) AS count_table1,
    (SELECT COUNT(*) FROM TABLE2) AS count_table2,
    (SELECT COUNT(*) FROM TABLE3) AS count_table3,
    etc..
    (SELECT COUNT(*) FROM TABLE19) AS count_table19

score 2 · Accepted Answer · edited May 23 '17 at 12:03

2

Depending on which DB this is it could slightly change...

For Oracle do something like:

Select (select count(*) from table1) as table1Count, 
       (select count(*) from table2) as table2Count
from dual

If it's SQL Server then just leave off the from dual.

EDIT:

Since you mentioned you are using MySQL in the comments:

Get record counts for all tables in MySQL database

edited May 23 '17 at 12:03

Community

1
1

answered Nov 09 '11 at 20:40

Kevin LaBranche

20,908
5
52
76

1

Used a solution from the link. – Matt Nov 09 '11 at 21:02

score 0 · Answer 3 · answered Sep 16 '13 at 11:37

If you are happy to accept the results in rows, not columns, you can always use a UNION:

SELECT "table1", COUNT(*) FROM table1 UNION                         
SELECT "table2", COUNT(*) FROM table2 UNION                         
SELECT "table3", COUNT(*) FROM table3

etc.

score 0 · Answer 4 · answered Sep 27 '13 at 07:23

0

On MySQL this works:

select sum(total) from(
select count(*) as total from Table1 
union
select count(*) as total from Table2) as a;

answered Sep 27 '13 at 07:23

Alberto

131
1
4

Count(*) Multiple Tables

4 Answers4