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
...
)

Giorgos Betsos · Accepted Answer · 2016-02-05T19:08:55.617

3

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

edited Feb 05 '16 at 19:08

answered Feb 05 '16 at 18:56

Giorgos Betsos

71,379
9
63
98

Great, thanks Giorgos! I had to make one change and remove the "`" from around the .hosts else I received this error: ERROR 1146 (42S02): Table 'test.companyA.hosts' doesn't exist – big horchata Feb 05 '16 at 19:17
@bighorchata This was a bug that I fixed afterwards. Never mind, the statement can also be executed without the '`' characters. – Giorgos Betsos Feb 05 '16 at 19:18
Thanks again, much appreciated – big horchata Feb 05 '16 at 19:19

score 1 · Answer 2 · edited Feb 14 '16 at 09:39

1

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

 ;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 cant use a common table expression then 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

edited Feb 14 '16 at 09:39

Mr Lister

45,515
15
108
150

answered Feb 05 '16 at 18:42

Fuzzy

3,810
2
15
33

Unfortunately MySQL does not support WITH clause. I should have made that clear that this is mysql (beyond the tag i already have) – big horchata Feb 05 '16 at 18:47
MySQL doesn't support the WITH clause (CTE in SQL Server parlance; Subquery Factoring in Oracle) – Haytem BrB Feb 05 '16 at 18:49
@bighorchata how about my second suggestion – Fuzzy Feb 05 '16 at 18:49
@KamranFarzami: So you stole my answer? great! – Haytem BrB Feb 05 '16 at 18:50
@haytem your answer would not work because you have not specified the schema. so in essence your answer is wrong. – Fuzzy Feb 05 '16 at 18:53
@KamranFarzami: No schemas here, this is not sql server. Plus, your first query is irrelevant. Great job downvoting my answer and then steal it. Some reading so you'll make your own answers next time: http://stackoverflow.com/questions/674115/select-columns-across-different-databases – Haytem BrB Feb 05 '16 at 18:53

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

2 Answers2