CODEWITHSUNDEEP
mysql
mossaab
mossaab

Reputation: 1834

Sum of counts from multiple tables

I have a dozen of tables with the same structure. All of their names match question_20%. Each table has an indexed column named loaded which can have values of 0 and 1.

I want to count all of the records where loaded = 1. If I had only one table, I would run select count(*) from question_2015 where loaded = 1.

Is there a query I can run that finds the tables in INFORMATION_SCHEMA.TABLES, sums over all of these counts, and produces a single output?

Upvotes: 1

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do what you want with dynamic SQL.

However, you have a problem with your data structure. Having multiple parallel tables is usually a very bad idea. SQL supports very large tables, so having all the information in one table is a great convenience, from the perspective of querying (as you are now learning) and maintainability.

SQL offers indexes and partitioning schemes for addressing performance issues on large tables.

Sometimes, separate tables are necessary, to meet particular system requirements. If so, then a view should be available to combine all the tables:

create view v_tables as
    select t1.*, 'table1' as which from table1 union all
    select t2.*, 'table2' as which from table2 union all
    . . .

If you had such a view, then your query would simply be:

select which, count(*)
from v_tables
where loaded = 1
group by which;

Upvotes: 1

Related Questions