How to combine two independent mysql count query result in a single view

Question

I have two independent queries as:

SELECT COUNT(*) FROM USER;

and

SELECT COUNT(*) FROM POSTS;

for these result I have created a mysql script file named as total.sql:

USE matchstix;
SELECT COUNT(*) FROM USER;
SELECT COUNT(*) FROM POSTS;

when I ran this script at mysql shell then getting results as

mysql>source /home/total.sql;

    Database changed
    +-------+
    |COUNT(*) |
    +-------+
    | 94122 |
    +-------+
    1 row in set (0.03 sec)

    +----------+
    | COUNT(*) |
    +----------+
    |    94007 |
    +----------+

the question is here how can i get the result something like:

+----------+------------+
| COUNT(*) | COUNT(*)   |
+----------+-------------
|    94007 |  94122     |
+----------+--------------
1 row in set (0.44 sec)

is it possible if yes then how ??

Sebastian Brosch · Accepted Answer · 2020-01-23T08:04:19.240

4

You can use a SELECT with sub-queries to show the totals in one row:

SELECT 
  (SELECT COUNT(*) FROM USER) AS count_user,
  (SELECT COUNT(*) FROM POSTS) AS count_post

demo on dbfiddle.uk

edited Jan 23 '20 at 08:04

answered Nov 22 '16 at 07:50

Sebastian Brosch

42,106
15
72
87

1

I would do the same. – Rahul Nov 22 '16 at 08:09

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

Try to use JOIN statement of SQL query. Basicly, you should write two select statement for two different table, and join them together into one view.

I would suggest you to take a look at this : https://stackoverflow.com/a/23700994/1867076

edited May 23 '17 at 10:33

Community

1
1

answered Nov 22 '16 at 07:50

Prometheus

1,522
3
23
41

1

thanks for you answer. but one thing i need to know how it is good rather then using sub-queries – Greesh Kumar Nov 22 '16 at 08:05
Join on what exactly? you don't need a join here – Rahul Nov 22 '16 at 08:09

How to combine two independent mysql count query result in a single view

2 Answers2