SQL query to count the number of multiple databases on the same host

Question

It seems like it should be simple and doable, but I'm not smart. I am trying to sum the number of hosts across several databases on the same server with one query. Databases that summarize the host count are themselves derived from the query.

get a list of databases:

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

Get the total 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 must use a prepared statement to get 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; 

Exiting SELECT @sql :

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

So, the @sql variable contains a dynamic sql statement that must be executed to get the desired result.

Demo here

Answer 2

Assuming the database names are correct and each database contains a table called Hosts, we still need to include the schema name in the query. So just replace <schema> with the name of the schema you have and run the query below and you should get the amount.

  ;WITH CTE AS ( select count(id) AS [HostSum] from companyA.<schema>.hosts UNION ALL select count(id) AS [HostSum] from companyB.<schema>.hosts UNION ALL select count(id) AS [HostSum] from companyC.<schema>.hosts ) SELECT SUM([HostSum]) AS [HostSum] FROM CTE 

If you cannot use a generic table expression, you can use the following:

 SELECT SUM([HostSum]) AS [HostSum] FROM ( select count(id) AS [HostSum] from companyA.<schema>.hosts UNION ALL select count(id) AS [HostSum] from companyB.<schema>.hosts UNION ALL select count(id) AS [HostSum] from companyC.<schema>.hosts ) AS A