SQL query to sum up count across multiple databases on same host

Question

This seems like it should be simple and doable but I'm not smart. I'm trying to sum up the count of hosts across multiple databases on the same server with a single query. The databases to sum up the host count are themselves derived from a query.

get a list of databases:

mysql> select name from db1.companies where status = 'active';
+---------------------+
| name                |
+---------------------+
| companyA            |
| companyB            |
| companyC            |
...

Get the total sum of the host count from each database:

SUM(
select count(id) from companyA.hosts
select count(id) from companyB.hosts
select count(id) from companyC.hosts
...
)

Answer 1

You have to use a prepared statement to get at the desired result:

SELECT
  GROUP_CONCAT(
    CONCAT(
      '(SELECT count(id) FROM `',
      name,
      '`.`hosts`)') SEPARATOR ' + ')
FROM
  db1.companies
WHERE
  status = 'active'
INTO @sql;

SET @sql := CONCAT('SELECT ', @sql);

SELECT @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Output from SELECT @sql:

@sql
-------------------------------------------------------------------------
SELECT (SELECT count(id) FROM `companyA`.`hosts`) + 
       (SELECT count(id) FROM `companyB`.`hosts`) + 
       (SELECT count(id) FROM `companyC`.`hosts`)

So, @sql variable holds the dynamic sql statement that needs to be executed in order to obtain the desired result.

Demo here