58

How do I go about selecting COUNT(*)s from multiple tables in MySQL?

Such as:

SELECT COUNT(*) AS table1Count FROM table1 WHERE someCondition
JOIN?? 
SELECT COUNT(*) AS table2Count FROM table2 WHERE someCondition
CROSS JOIN? subqueries?
SELECT COUNT(*) AS table3Count FROM table3 WHERE someCondition

Edit:

The goal is to return this:

+-------------+-------------+-------------+
| table1Count | table2Count | table3Count |
+-------------+-------------+-------------+
| 14          | 27          | 0           |
+-------------+-------------+-------------+
bcmcfc
  • 25,966
  • 29
  • 109
  • 181

7 Answers7

130

You can do it by using subqueries, one subquery for each tableCount :

SELECT
  (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, 
  (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count,
  (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3Count
Julien Hoarau
  • 48,964
  • 20
  • 128
  • 117
12

You can do this with subqueries, e.g.:

select (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, 
       (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
8

Here is simple approach to get purely the row counts from multiple tables, if there are no conditions on specific tables.

Note:

For InnoDB this count is an approximation. However, for MyISAM the count is accurate.

Quoted from the docs:

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

Using the information_schema.tables table you can use:

SELECT 
    table_name, 
    table_rows
FROM 
    information_schema.tables
WHERE
    table_name like 'my_table%';

Output:

table_name    table_rows
my_table_1    0
my_table_2    15
my_table_3    30
S3DEV
  • 8,768
  • 3
  • 31
  • 42
5

You can use UNION

  SELECT COUNT(*) FROM table1 WHERE someCondition
  UNION
  SELECT COUNT(*) FROM table2 WHERE someCondition
  UNION
  SELECT COUNT(*) FROM table3 WHERE someCondition
Interfector
  • 1,868
  • 1
  • 23
  • 43
  • 5
    Union will give the results in individual rows instead of columns. The resultset the OP is looking for is a single row with 3 count columns – Brendan Bullen Sep 21 '10 at 15:19
4

You can do this in this way.

SELECT (select count(*) from table1) + (select count(*) from table2) as total_rows

You can add as many tables as you want.

Atul Tripathi
  • 63
  • 5
0

Try changing to:

SELECT 
    COUNT(table1.*) as t1,
    COUNT(table2.*) as t2,
    COUNT(table3.*) as t3 
FROM table1 
    LEFT JOIN tabel2 ON condition
    LEFT JOIN tabel3 ON condition
the Tin Man
  • 158,662
  • 42
  • 215
  • 303
Pramendra Gupta
  • 14,667
  • 4
  • 33
  • 34
  • 1
    This will not work because all `counts` will return the same number for all three tables because it counts the same resultset – ilija veselica Jul 05 '18 at 12:05
  • 1
    This is wrong. if you join multiple tables like this , it will multiply each record and will return false count for each – Hashan Mar 01 '19 at 04:58
0

Not directly, but it works...

1.- Create a list of the tables you need to count rows.

2.- Put that list in the first column of a spreadsheet.

3.- In the second column, first row, use this formula:

="SELECT '"&A1&"', (SELECT count(*) FROM "&A1&") UNION"

4.- Fill down the formula.

5.- Copy the second column and paste it to do the query.

6.- Don't forget to remove the last UNION.

Nezumi
  • 11
  • 3