COUNT(*) from multiple tables in MySQL
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
Mysql COUNT(*) 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
MySQL join and COUNT() on multiple tables
You will need to use DISTINCT
, but also you need to count the IDs, not the foreign keys:
SELECT
table1.name,
COUNT(DISTINCT table2.id) AS table2_count,
COUNT(DISTINCT table3.id) AS table3_count,
COUNT(DISTINCT table4.id) AS table4_count,
SUM(table4.size) AS table4_size
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1_id
LEFT JOIN table3 ON table2.id = table3.table2_id
LEFT JOIN table4 ON table3.id = table4.table3_id
WHERE table1.id = 1
Here is a fiddle.
Explanation: The DISTINCT
key word eliminates all duplicate values resulting in a list of unique values.
If you run your query without the COUNT()
and SUM()
, you get:
name table1_id table2_id table3_id size
test 1 1 1 1024
test 1 1 1 200
test 1 (null) (null) (null)
test 1 (null) (null) (null)
So if you add the COUNT()
and SUM()
, you obviously get:
name table1_id table2_id table3_id size
test 4 2 2 1224
However, using DISTINCT
with your query won't help because you can clearly see the duplicate values, which will result in:
name table1_id table2_id table3_id size
test 1 1 1 1224
Now, if you run my query without the COUNT()
and SUM()
, you get:
name table1_id table2_id table3_id size
test 1 1 1 1024
test 1 1 2 200
test 2 (null) (null) (null)
test 3 (null) (null) (null)
If you add the COUNT()
and SUM()
, you get exactly the same results like your query:
name table1_id table2_id table3_id size
test 4 2 2 1224
However, because this time you have different values (i.e. not all are 1), so now if you count the unique values using DISTINCT
, you get:
name table1_id table2_id table3_id size
test 3 1 2 1224
Mysql Query select count(*) from multiple tables
Within the various selects, keep the same column order.
So
SELECT a1.id as movie_id,
a1.movie_title as movie_title,
COUNT(*) AS free_cnt
0 as plus_cnt,
0 as visitor_cnt
FROM movie_request a1
GROUP BY a1.movie_title
UNION ALL
SELECT a2.id as movie_id,
a2.movie_title as movie_title,
0 as free_cnt,
COUNT(*) AS plus_cnt,
0 as visitor_cnt
FROM movie_request_2 a2
GROUP BY a2.movie_title
UNION ALL
SELECT a3.id as movie_id,
a3.movie_title as movie_title,
0 as plus_cnt,
0 as free_cnt,
COUNT(*) AS visitor_cnt
FROM movie_request_3 a3
GROUP BY a3.movie_title
multiple count from multiple tables and also group by grade
Do the counting in subqueries, and join the subqueries.
SELECT e.grade, english_count, hindi_count
FROM (SELECT grade, COUNT(*) AS english_count
FROM 2015_2016_x_english_grades
WHERE test_name = 'ut1_marks'
GROUP BY grade) AS e
JOIN (SELECT grade, COUNT(*) as hindi_count
FROM 2015_2016_x_hindi_grades
WHERE test_name = 'ut1_marks'
GROUP BY grade) AS h
ON e.grade = h.grade
Or if there's a unique key in each table, you can do:
SELECT e.grade, COUNT(DISTINCT e.id) AS english_count, COUNT(DISTINCT h.id) AS hindi_count
FROM 2015_2016_x_english_grades AS e
JOIN 2015_2016_x_hindi_grades AS h ON e.grade = h.grade AND e.test_name = h.test_name
WHERE e.test_name = 'ut1_marks'
GROUP BY e.grade
Note that both of these queries will only show a grade if it exists in both tables. To get grades that only exist in one table, you need a FULL OUTER JOIN
, but MySQL doesn't have this operation. See
Full Outer Join in MySQL
for how to emulate them.
Sort the table by COUNT after displaying values from multiple tables
mySQL extends the group by so you don't have to have one. However, it assumes all values for each column are the same; so it's free to pick what to put in from each column. However, if the values are different, what it picks (1 value) isn't representative of the entire set, so you must use group by when the values are different.
Put a different way: if t.forum_ID = 156 limited to a specific topic_poster, user_Id and username. you'd have no problem. But since t.forum_ID represents many different values in each of those columns, group by is needed or the engine will "somewhat" randomly select a value for each of them. The engine assumes all are the same.
Thus the downfall of the mySQL Group by extension. But, if all the non-aggregrated columns did have the same value... you get a performance gain by allowing the engine to just aggregate and 'pick' a value for each column.
Based on your response, you think you should be getting multiple rows. So that tells me the non-aggregated fields are different so add a group by...
SELECT t.topic_poster, COUNT(t.topic_poster), u.user_id, u.username
FROM phpbb_topics t
LEFT JOIN phpbb_users u ON u.user_id = t.topic_poster
WHERE t.forum_id = 156
GROUP BY t.topic_poster, u.user_id, u.username
ORDER BY COUNT(t.topic_poster)
You could have ties, so you may also want to order by poster or user name after the count...
Selecting Counts from Different Tables with a Subquery
This is a case where I would recommend correlated subqueries:
select a.userid,
(select count(*) from table1 t1 where t1.userid = a.userid) as cnt1,
(select count(*) from table2 t2 where t2.userid = a.userid) as cnt2
from allusers a
where a.vip is not null;
The reason that I recommend this approach is because you are filtering the alllusers
table. That means that the pre-aggregation approach may be doing additional, unnecessary work.
Related Topics
Differencebetween Select and Project Operations
How to Do a Find/Replace in T-Sql
SQL Full Text Search VS "Like"
How to Model Custom Attributes of Entities
Delete the 'First' Record from a Table in SQL Server, Without a Where Condition
Is Substr or Like Faster in Oracle
SQL Query in Spark/Scala Size Exceeds Integer.Max_Value
Select Count of Rows in Another Table in a Postgres Select Statement
How to Execute Table Valued Function
How to Select Using with Recursive Clause
What Is the Most Efficient Way to Write a Select Statement with a "Not In" Subquery
SQL Query to Add a New Column After an Existing Column in SQL Server 2005
How to Check If a Table Is Locked in SQL Server
How to Parse/Tokenize an SQL Statement in Node.Js