Same table union from different databases

I have 03 MySQL databases as 2022_db, 2023_db and 2024_db. All the tables and table structures in the 03 databases are same & included different values.I want to get the sum of values in two tables in 02 databases 2022_db and 2023_db into a single report. Tables are named as vote & finacne_budget_issue. The columns are as vote and amount. I used the following query to do that.

 public function amt_tot()
{
    $otherdb2022 = $this->load->database('2022_db', TRUE);
    $otherdb2023 = $this->load->database('2023_db', TRUE);
    $q = $this->db->query("
     select 
            SELECT finance_vote.vote, SUM(amount) AS amount
            FROM (
            SELECT finance_vote.vote, SUM(amount) AS amount AS amt_2022
            FROM $otherdb2022.finance_budget_issue
            GROUP BY vote
            UNION ALL
            SELECT finance_vote.vote, SUM(amount) AS amount AS amt_2023
            FROM $otherdb2023.finance_budget_issue
            GROUP BY vote
    ) AS tot
    GROUP BY vote");

    if ($q->num_rows() > 0) {
        $results = $q->result();
        return $results;
    }
}

But the query says that systax error "near ‘SELECT vote, SUM(amount) AS amount FROM….. ". What may be going wrong in my query ? Can any one help this for me ?

>Solution :

You have double aliased the sums, e.g.

SUM(amount) AS amount AS amt_2022

However, a better pattern to use here would be to union the two tables first, then aggregate that as a subquery and take the totals:

SELECT vote, SUM(amount) AS amount
FROM (
    SELECT vote, amount
    FROM table_2022
    UNION ALL
    SELECT vote, amount
    FROM table_2023
) t
GROUP BY vote;

By the way, it is generally bad practice to bind a table name using a PHP variable. This opens your code to outside SQL injection attacks. That being said, if you are certain that $otherdb2022 and $otherdb2023 would not be getting populated from outside data, then it might be safe.