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 |
+-------------+-------------+-------------+
asked Sep 21, 2010 at 14:20
bcmcfcbcmcfc
24.9k28 gold badges109 silver badges176 bronze badges
2
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
answered Sep 21, 2010 at 14:22
Julien HoarauJulien Hoarau
47.8k20 gold badges126 silver badges117 bronze badges
6
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
answered Sep 21, 2010 at 14:27
D'Arcy RittichD'Arcy Rittich
162k38 gold badges283 silver badges279 bronze badges
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
answered Mar 11, 2020 at 9:29
S3DEVS3DEV
7,6653 gold badges27 silver badges36 bronze badges
You can use UNION
SELECT COUNT[*] FROM table1 WHERE someCondition
UNION
SELECT COUNT[*] FROM table2 WHERE someCondition
UNION
SELECT COUNT[*] FROM table3 WHERE someCondition
answered Sep 21, 2010 at 14:22
InterfectorInterfector
1,8021 gold badge23 silver badges41 bronze badges
1
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.
answered May 17, 2021 at 0:54
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
156k41 gold badges209 silver badges297 bronze badges
answered Sep 21, 2010 at 14:30
Pramendra GuptaPramendra Gupta
14.3k4 gold badges32 silver badges34 bronze badges
2