How to write dynamic query in SQL inorder to get the sum of same columns from different tables by us-CodePudding

I want to write a query to get to know the sums of same columns across all the tables.

select sum(column1),sum(column2) from
(
  select t1.column1 as column1, 0.00 as column2 from table1 t1
  union all
  select 0.00 as column1, t2.column1 as column2 from table2 t2
)

Tried below query

select t1.column1, t2.column1 from table1 t1, table2 t2

but it is not working and it is taking a very very long time

Please suggest to me the best approach.

CodePudding user response：

With union or complex queries, you may get long time responses. Instead of union or join, you can use multiple tables in one FROM condition. I may suggest two example methods for your question:

1. For separately total column sum response:

Select
    sum(categories.id),
    sum(form_elements.id),
    sum(users.id)
From users,categories,form_elements;

2. Total sum of all table columns:


Select
    ( sum(categories.id)  
    sum(form_elements.id)  
    sum(users.id) ) as total_sum
From users,categories,form_elements

CodePudding user response：

How can i make the below query as a dynamic query?

select t1.cnt, t2.cnt from ( select count() cnt from table1 where... ) t1 , ( select count() cnt from table2 where... ) t2

It is working to know the sum of the column from different tables but i need to make this one as a dynamic query.

Please help me to find the answer..