June 3, 2008 at 1:54 pm
Hi
I am trying to join 2 tables & also get the sum of the columns in each table. The Problem is the sum is not correct...
Ex: table-1
Id date value
-----------------------------
12008-01-01 10
22008-01-01 10
32008-01-07 15
Table-2
id date value
----------------------------
12008-01-01 25
22008-01-21 25
My sql
select u.date as date, sum(t1.value) as c1v, sum(t2.value) as c2v
from (select date from table_1 union select date from table_2) u
left outer join table_1 t1 on u.date = t1.date
left outer join table_2 t2 on u.date = t2.date
group by u.date
result
date cv1 cv2
---------------------------
2008-01-01 2050
2008-01-07 15NULL
2008-01-21 NULL25
Question? date 2008-1-1 should have 25 in CV2 why it is getting 50.
any help will be greatly appreciated.
Thanks
June 3, 2008 at 2:26 pm
Try this:
;with
T1 (Date, C1V) as
(select date, sum(value)
from table_1
group by date),
T2 (Date, C2V) as
(select date, sum(value)
from table_2
group by date)
select isnull(t1.date, t2.date) as Date,
isnull(c1v, 0) as C1V,
isnull(c2v, 0) as C2V
from T1
full outer join T2
on T1.Date = T2.Date
The reason you are getting 50 is because it does the join before it does the sum. So you end up with 2 rows of 25 each (because of the join), then it sums that up and groups it, and ends up with 50. This way, it does the sum first, then the join, so it should get 25.
Try it out, let me know if it works or not.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2008 at 2:36 pm
Thanks....This is what I was looking for. Results are perfect.
Thanks also for making me understand the process.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply