Obtain the sum of count of multiple schema and tables in one query in mysql 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
Here's an ugly solution:
SELECT MAX(tmp.col1) as col1,
       MAX(tmp.col2) as col2,
       MAX(tmp.col3) as col3,
       MAX(tmp.col4) as col4,
       MAX(tmp.col1) + MAX(tmp.col2) + MAX(tmp.col3) + MAX(tmp.col4) as total
  FROM (SELECT COUNT(*) AS col1, NULL as col2, NULL as col3, NULL as col4
          FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, COUNT(*) AS col2, NULL as col3, NULL as col4
          FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, NULL as col2, COUNT(*) AS col3, NULL as col4
          FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1
         UNION ALL
        SELECT NULL as col1, NULL as col2, NULL as col3, COUNT(*) AS col4
          FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1) tmp

This will give you something like:




col1
col2
col3
col4
total




10
20
30
40
100




It's not very efficient, but it gets the job done.