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

this seems should simple , doable i'm not smart. i'm trying sum count of hosts across multiple databases on same server single query. databases sum host count derived query.

get list of databases:

mysql> select name db1.companies status = 'active'; +---------------------+ | name                | +---------------------+ | companya            | | companyb            | | companyc            | ... 

get total sum of host count each database:

sum( select count(id) companya.hosts select count(id) companyb.hosts select count(id) companyc.hosts ... ) 

you have use prepared statement @ desired result:

select   group_concat(     concat(       '(select count(id) `',       name,       '`.`hosts`)') separator ' + ')   db1.companies   status = 'active' @sql;  set @sql := concat('select ', @sql);  select @sql;  prepare stmt @sql; execute stmt; 

output select @sql:

@sql ------------------------------------------------------------------------- select (select count(id) `companya`.`hosts`) +         (select count(id) `companyb`.`hosts`) +         (select count(id) `companyc`.`hosts`) 

so, @sql variable holds dynamic sql statement needs executed in order obtain desired result.

demo here