February 12, 2013 at 6:59 am
All,
I have some queries as below.
create table nav
(
id int,
amt decimal(16,8)
)
insert into nav
select 1,100
union all
select 1,200
create table balance
(
id int,
amt decimal(16,8)
)
insert into balance
select 1, 10
select a.id, sum(a.amt) + sum (b.amt)
from nav a, balance b
where a.id = b.id
group by a.id
Output:
1 320
Expected Output :
1 310
As I am using JOIN, I am getting this value. I can rewrite my query as below
select id, sum(amt) as amt from
(
select id, sum(amt) as amt
from nav
group by id
union
select id, sum(amt)
from balance
group by id
)z
group by id
or else..I can use CTE or some other features of SQL 2008. But my intention is to know is there any techniques available to bring the expected result set by using simple join itself.
i.e I have to play and would like to apply some techniques not the features like CTE, TABLE VARIABLES, VALUES, CROSS APPLY, PIVOT or anything..
select a.id, sum(a.amt) + sum (b.amt)
from nav a, balance b
where a.id = b.id
group by a.id
Based on some techniques, I would like to get the result by using the above query.
karthik
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply