CODEWITHSUNDEEP
sql
jeswin
jeswin

Reputation: 382

SQL Query for finding the sum from two tables

I have two tables both having sequence ids for identifying corresponding rows. But one table is having data entry in the form of one to many and other having data in the form of one to one. I need to find the sum of values corresponding to particular sequence ids.

the tables are like

table1

seq_id      amt1      amt2
  222        0         100 
  223        0         200
  224       300         0

table2

seq_id     code       amt3
 222        001        100
 222        002        150
 223        001        100

I need to find the sum of amt1+amt2+amt3 of corresponding ids from both tables

ie the output may look like

total_amount
850

Upvotes: 1

Views: 98

Answers (6)

user330315
user330315

Reputation:

This will show the sum for all seq_id values that show up in table1

select t1.seq_id, sum(t1.amt1 + t1.amt2 + coalesce(t2.amt3)) as total_amount
from table1 t1
  left join table2 t2 on t1.seq_id = t2.seq_id
group by t1.seq_id;

If there are seq_id values in table2 that are not in table1 you need a full outer join. In that case you also need to take care of null values in t1:

select t1.seq_id, sum(coalesce(t1.amt1,0) + coalesce(t1.amt2,0) + coalesce(t2.amt3)) as total_amount
from table1 t1
  full join table2 t2 on t1.seq_id = t2.seq_id
group by t1.seq_id;

If you want to sum all values indepently of their seq_id, then use a union:

select sum(amount) as total_amount
from (
   select amt1 + amt2 as amount
   from table1
   union all
   select amt3 as amount
   from table2
) t

Upvotes: 2

Mykhailo Marufenko
Mykhailo Marufenko

Reputation: 61

I came up with easier

SELECT 
(SELECT SUM(atm1) FROM table1)+
(SELECT SUM(amt2) FROM table1)+
(SELECT SUM(amt3) FROM table2)
AS remain

Upvotes: 0

I A Khan
I A Khan

Reputation: 8839

use this

select table1.seq_id, sum(table1.amt1 + table1.amt2 + coalesce(table2.amt3)) as total_amount
from table1
  left join table2 on table1.seq_id = table2.seq_id
group by table1.seq_id;

Upvotes: 0

Recursive
Recursive

Reputation: 952

Try below query with JOIN

SELECT IFNULL(a.amt1,0) + IFNULL(a.amt2,0) + IFNULL(b.amt3,0) as total_amount 
FROM table1 a JOIN table2 b 
ON a.seq_id=b.seq_id

Upvotes: 0

Mureinik
Mureinik

Reputation: 311308

A couple of nested queries should do the trick:

SELECT a.seq_id, a_sum + b_sum AS total_sum
FROM   (SELECT   seq_id, amt1 + amt2 AS a_sum
        FROM     table1) a -- no GROUP BY, as seq_id is unique in this table
JOIN   (SELECT   seq_id, SUM(amt3) AS b_sum
        FROM     table2
        GROUP BY seq_id) b ON a.seq_id = b.seq_id

Upvotes: 1

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

declare @table1 table (seq_id INT, amt1 decimal(18,2), amt2 decimal(18,2))
declare @table2 table (seq_id INT, code INT, amt3 decimal(18,2))
insert into @table1(seq_id, amt1, amt2)
values
(222 , 0 , 100),
(223 , 0 , 200),
(224 , 300, 0)

insert into @table2(seq_id, code, amt3)
values
(222 , 001 , 100),
(222 , 002 , 150),
(223 , 001 , 100)

select distinct
   t1.seq_id,
   t1.amt1 + t1.amt2 + t2.amt3
from @table1 t1
   join @table2 t2 on t2.seq_id = t1.seq_id

select SUM(t1.amt1) + SUM(t1.amt2) + SUM(t2.amt3)
from @table1 t1
   join @table2 t2 on t2.seq_id = t1.seq_id

Upvotes: 0

Related Questions