removing duplicates while summing up
658306Sep 5 2008 — edited Sep 5 2008I have got a question,
Actually, i am joining three tables in my query.
Example:
Table A:
Col1 Col2
X 10
Table B:
Col1 Col2 Col3
X Y 15
X Z 25
Table C:
Col1 Col2 Col3 Col4
S X Y 150
R X Y 30
S X Z 100
R X Z 250
SQL:
Select a.col1,sum(b.col3), sum(Case when c.col1 = 'S' then c.col4 else 0 end) s,
sum(Case when c.col1 = 'R' then c.col4 else 0 end) r
from tbl1 a, tbl2 b, tbl3 c where
a.col1 = b.col1 and b.col1 = c.col2 and b.col2 = c.col3
group by a.col1
Ans:
a.col1 sum(b.col3) S R
X 80 250 280
As mentioned above i am getting sum value as 80 in sum(b.col3) whereas it should be 40. This is because of duplicate rows in table C. How can i make it better?
Any help thanks in advance..