Obtain the sum of count of multiple schema and tables in one query in mysql

Question

The query belows return 4 rows and it's good. But i need also to sum the results of all the counts in the same query. How to achieve it? I've tried different things but i get sintax errors.

SELECT COUNT(*) FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1

Answers

1. Fewer keystrokes:

SELECT s1, s2, s3, s4,
       s1 + s2 + s3 + s4 AS total
    FROM ( SELECT
        ( SELECT COUNT(*) FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s1,
        ( SELECT COUNT(*) FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s2,
        ( SELECT COUNT(*) FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s3,
        ( SELECT COUNT(*) FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s4
         ) AS counts;

If there is a performance question, be sure to have INDEX(state, level) on each table. (The order of the columns in the index does not matter in this case.)

If this is just one of many clumsy queries, you may want to reconsider having multiple databases (schemas) for the data.

2. Combine your sub-queries and put a SELECT clause on it.

SELECT
(SELECT COUNT(*) AS cnt FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1) s1
,
(SELECT COUNT(*) AS cnt FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1) s2
,
(SELECT COUNT(*) AS cnt FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1) s3
,
(SELECT COUNT(*) AS cnt FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1) s4

That should give you single row with 4 columns in it.

If you need sum of them in single query then use below query.

You can put them into a select clause and it should give you sum of it. here is an example.

SELECT SUM(cnt) FROM
(
SELECT COUNT(*) AS cnt FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) AS cnt FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) AS cnt FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
UNION ALL
SELECT COUNT(*) AS cnt FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1
) tmp

内容来源于Database Administrators Stack Exchange, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接： Obtain the sum of count of multiple schema and tables in one query in mysql