MySQL – COUNT(*) from Multiple Tables

countmysql

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           |
+-------------+-------------+-------------+

Best Answer

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

Related Solutions

MySQL COUNT(*) – How to Use on Multiple Tables

Using subselects you can do:

SELECT co.*, 
    (SELECT COUNT(*) FROM modules mod WHERE mod.course_id=co.id) AS moduleCount, 
    (SELECT COUNT(*) FROM videos vid WHERE vid.course_id=co.id) AS vidCount
FROM courses AS co
ORDER BY co.id DESC

But be carefull as this is an expensive query when courses has many rows.

EDIT: If your tables are quite large the following query should perform much better (in favor of being more complex to read and understand).

SELECT co.*, 
    COALESCE(mod.moduleCount,0) AS moduleCount,
    COALESCE(vid.vidCount,0) AS vidCount
FROM courses AS co
    LEFT JOIN (
            SELECT COUNT(*) AS moduleCount, course_id AS courseId 
            FROM modules
            GROUP BY course_id
        ) AS mod
        ON mod.courseId = co.id
    LEFT JOIN (
            SELECT COUNT(*) AS vidCount, course_id AS courseId 
            FROM videos
            GROUP BY course_id
        ) AS vid
        ON vid.courseId = co.id
ORDER BY co.id DESC

Best Answer

Related Solutions

MySQL COUNT(*) – How to Use on Multiple Tables

Related Question