February 12, 2013 at 7:47 am
This shows why you're getting 320
select a.id, a.amt, b.amt
from nav a
inner join balance b
on a.id = b.id
This is an example to return the expected result of 310 using simple joins with inline tables.
SELECT a1.id, a1.total + b1.total
FROM (SELECT a.id, sum(a.amt) total
FROM nav a
GROUP BY a.id)a1
INNER JOIN (SELECT b.id, SUM(b.amt) total
FROM balance b
GROUP BY b.id)b1
ON a1.id = b1.id
February 12, 2013 at 7:50 am
You need a union all as there is a chance that the amount in the bottom and top are the same, also you dont need to do the sum in the Sub select do it on the outer select only.
SELECT Id, SUM(amt)
FROM
(SELECT Id,Amt
FROM nav
UNION ALL SELECT Id,Amt
FROM balance
) base
Group by Id
Edit : Select in the Union missing
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 12, 2013 at 8:00 am
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply