I have a sql statement that will give me two columns from two tables using sub query.
select
sum(field1) as f1_sum,
(select sum(field2) from table2) as f2_sum
from
table1
group by
table1.field_x
I want to get the total of f1_sum + f2_sum as the third column output from this query. It seems simple but I can't find a way around this.Question is how to get the sum of sum fields.
I am ok to write SP or a view to do this etc..
Can someone assist please ?
you can use subquery like:
SELECT t1.f1_sum+t1.f2_sum AS total_sum FROM
(select sum(field1) as f1_sum , (select sum(field2) from table2) as f2_sum
from table1
group by table1.field_x) AS t1
I would suggest doing it like this:
select t1.f1_sum, t2.f2_sum, coalesce(t1.f1_sum, 0) + coalesce(t2.f2_sum, 0)
from (select sum(field1) as f1_sum
from table1 t1
group by t1.field_x
) t1 cross join
(select sum(field2) as f2_sum from table2) t2;
When possible, I prefer to keep table references in the from clause. I added the coalesce() just in case any of the values could be NULL.
You could also try this :
SELECT SUM(a.field1) f1_sum,
SUM(b.field2) f2_sum,
(SUM(a.field1) + SUM(b.field2)) f3_sum
from table1 a, table2 b
Simply you can write,
select sum(field1) as f1_sum
, (select sum(field2) from table2) as f2_sum
, (ISNULL(sum(field1),0) + ISNULL((select sum(field2) from table2),0)) AS Total_Sum
from table1
group by table1.field_x
