How to Combine COUNT(*) from Multiple Tables in MySQL?

Combining COUNT(*) from Multiple Tables in MySQL

In MySQL, you can retrieve the count of rows from multiple tables by leveraging a combination of subqueries. Here's how it's done:

For each table that you want to count rows from, create a subquery that selects COUNT(*):

<code class="sql">(SELECT COUNT(*) FROM table1 WHERE someCondition) AS table1Count</code>

Combine these subqueries into a main SELECT statement to get the counts:

<code class="sql">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</code>

This will return a table with three columns, each representing the count of rows in the corresponding table.

Example:

Let's say you have the following tables and conditions:

<code class="sql">table1:
WHERE someCondition = True

table2:
WHERE someCondition = False

table3:
WHERE someCondition = True</code>

Using the above subquery technique, you would get the following result:

<code class="sql">+-------------+-------------+-------------+
| table1Count | table2Count | table3Count |
+-------------+-------------+-------------+
| 14          | 27          | 0           |
+-------------+-------------+-------------+</code>

This demonstrates how you can effectively obtain the counts from multiple tables in a single query.

The above is the detailed content of How to Combine COUNT(*) from Multiple Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!