CODEWITHSUNDEEP
sqlsql-server
BenW
BenW

Reputation: 1453

MS SQL Server sum of sum fields

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 ?

Upvotes: 0

Views: 507

Answers (4)

Bhavesh Harsora
Bhavesh Harsora

Reputation: 693

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

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.

Upvotes: 1

Saechel
Saechel

Reputation: 152

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

Upvotes: 0

bksi
bksi

Reputation: 1625

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

Upvotes: 1

Related Questions