bcmcfc bcmcfc - 1 year ago 71
MySQL Question

COUNT(*) from multiple tables in MySQL

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

Such as:

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


The goal is to return this:

| table1Count | table2Count | table3Count |
| 14 | 27 | 0 |

Answer Source

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

  (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, 
  (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count,
  (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3Count
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download