CODEWITHSUNDEEP
mysql
Mac Taylor
Mac Taylor

Reputation: 5148

Counting different tables in MyISAM MySQL tables

I wrote the code below as an example to fetch from different MyISAM MySQL tables and I can't use UNION to count and only the first value turns correct and the other is empty.

Actually which is first and that's because I heard my tables are MyISAM.

(SELECT COUNT(*) AS userCount from table_users)
UNION 
(SELECT COUNT(*) AS totalposts from table_stories)

So what's the solution?

Upvotes: 0

Views: 133

Answers (1)

Daniel Vassallo
Daniel Vassallo

Reputation: 344291

If you want both results in the same row, you could use subqueries for that. Test case:

CREATE TABLE table_users (id int) ENGINE=MYISAM;
CREATE TABLE table_stories (id int) ENGINE=MYISAM;

INSERT INTO table_users VALUES (1), (2), (3);
INSERT INTO table_stories VALUES (1), (2), (3), (4), (5), (6);

SELECT (SELECT COUNT(*) from table_users) userCount,
       (SELECT COUNT(*) from table_stories) totalposts;
+-----------+------------+
| userCount | totalposts |
+-----------+------------+
|         3 |          6 |
+-----------+------------+
1 row in set (0.00 sec)

If on the other hand you want your results in separate rows, the query that you were using should work as well:

(SELECT COUNT(*) AS count_value from table_users)
UNION
(SELECT COUNT(*) from table_stories);
+-------------+
| count_value |
+-------------+
|           3 |
|           6 |
+-------------+
2 rows in set (0.02 sec)

Upvotes: 1

Related Questions