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-05 19:08:55Z

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 5, 2016 at 19:08

answered Feb 5, 2016 at 18:56

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

big horchata Over a year ago

Great, thanks Giorgos! I had to make one change and remove the "`" from around the <database>.hosts else I received this error: ERROR 1146 (42S02): Table 'test.companyA.hosts' doesn't exist

Giorgos Betsos Over a year ago

@bighorchata This was a bug that I fixed afterwards. Never mind, the statement can also be executed without the '`' characters.

big horchata Over a year ago

Thanks again, much appreciated

Mr Lister · Accepted Answer · 2016-02-14 09:39:55Z

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, 2016 at 9:39

Mr Lister

46.8k15 gold badges118 silver badges156 bronze badges

answered Feb 5, 2016 at 18:42

Fuzzy

3,8102 gold badges17 silver badges34 bronze badges

6 Comments

big horchata Over a year ago

Unfortunately MySQL does not support WITH clause. I should have made that clear that this is mysql (beyond the tag i already have)

Haytem BrB Over a year ago

MySQL doesn't support the WITH clause (CTE in SQL Server parlance; Subquery Factoring in Oracle)

Fuzzy Over a year ago

@bighorchata how about my second suggestion

Haytem BrB Over a year ago

@KamranFarzami: So you stole my answer? great!

Fuzzy Over a year ago

@haytem your answer would not work because you have not specified the schema. so in essence your answer is wrong.

|

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related