Please help me write this query

[SkyHigh]

Hi Folks

I have, suppose, three tables with same schema with data for different quarters, and I have to take sum of one of the columns in all three tables and I am sending the following query which returns 3 sums from 3 tables I am looking for a way to get a total of these 3 sums as one amount with just one query, creating a view/temp table is out of question, because table names could be different depending on the date range the data is requested for :

select sum(col1) from table1
union
select sum(col1) from table2
union
select sum(col1) from table3;

is there a way I can do it with just one query, I am willing to consider creating a PL/PGSQL function to do it, if someone can show me how - thanks

Brenda

 

[dsanchez2]

I think the following should work:


select sum(col1)
from (select table1_amount col1 from table1
union
select table2_amount col1 from table2
union
select table3_amount col1 from table3)


Or you could create a view for the "union" and then do your "select sum" on the view.

 

[SkyHigh]

Thanks, I tried it but it results in the following error:

ERROR: sub-select in FROM must have an alias

Rgds
Brenda

 

[ceco]

so give it an alias ;-)

select sum(col1)
from (select table1_amount as col1 from table1
union
select table2_amount as col1 from table2
union
select table3_amount as col1 from table3) AS u1

a put u1 as an alias name in the example