SUM values of specific column from all tables LIKE table_%

Question

I need help to create an SQL query in order to SUM the values of specific column from all tables LIKE table_% as the tables will grow over time and this must cater for new table names based on the format below

---

Scheme Name: database_01

Table Names: tb_data_'YEAR'_'MONTH'

---

• YEAR and MONTH are both values which range from all 12 months and years from 2011 to 2018.

Each Table contains a column called TOTAL_VALUE. I have a php script that triggers an SQL query to pull data from the database.

I would like to SUM the total of each tables TOTAL_VALUE column and save the value for my script below to push the array.

$sql = "SELECT TOTAL_VALUES FROM tb_data_2017_october";   
$result = mysqli_query($conn, $sql);

$data = array(); while($enr = mysqli_fetch_assoc($result)){
    $a =  array($enr['TOTAL_VALUES']);
    foreach ($a as $as){
        echo "'".$as."', ";}
    array_push($data, $as); }

I have been trying to alter the SQL with options such as:

SELECT id FROM table1
    UNION
    SELECT id FROM table2
    UNION
    SELECT id FROM table3
    UNION
    SELECT id FROM table4

However i need to cater for the ability to check all tables that are like tb_data_%

Answer 1

Use the MERGE storage engine to create a virtual table that combines all the monthly tables.

CREATE TABLE tb_all_data (
    ...
) ENGINE=MERGE UNION=(tb_data_2017_october, tb_data_2017_november, ...);

List all the tables in the UNION= list, and update it whenever you create a new table.

Then you can just query from tb_all_data.

Answer 2

Try this- it will loop through all the tables with the pattern you want and create sums for you:

declare @table table (rowid int identity, name varchar(max))
        insert @table
        select name from sys.tables where name like '%yourname%'

        declare @holding table (name varchar(max), sumvalue int)

        declare @iterator int = 1
        declare @tablename varchar(max)
        while @iterator<=(select max(rowid) from @table)
        begin
        select @tablename=name from @table where rowid=@iterator

        insert @holding
        exec('select '+@tablename+' sum(TOTAL_VALUE)TOTAL_VALUE from '+@tablename+' group by +'+@tablename+'')

        set @iterator=@iterator+1
        end 

    select * from @holding

Answer 3

See this question for information about getting the list of tables: Get table names using SELECT statement in MySQL

You can get the list of tables in one query result, and then query each table. I'll rework your code slightly to give an example:

// Get the tables
$tables_sql = "SELECT table_name 
                FROM information_schema.tables 
                WHERE table_schema='<your DB>' 
                AND table_name LIKE 'tb_data%'";  

$tables = mysqli_query($conn, $sql);

// Iterate over the tables
while($table = mysqli_fetch_assoc($tables)){
{
    /*
    * Your code
    */

    // This query assumes that you can trust your table names not to to an SQL injection
    $sql = "SELECT TOTAL_VALUES FROM " . $table['table_name']; 
    $result = mysqli_query($conn, $sql);

    $data = array(); while($enr = mysqli_fetch_assoc($result)){
        $a =  array($enr['TOTAL_VALUES']);
        foreach ($a as $as){
            echo "'".$as."', ";
        array_push($data, $as); }
}

You can do whatever you need once your have your list of tables. You can build one big union query (which would be more efficient than querying each table individually), or feed the tables to the MERGE engine, as in barmar's answer