Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kieran5405
Yak Posting Veteran
96 Posts |
 Posted - 2014-07-23 : 07:06:02
|
| Hi,I have the below select statement (simplified) - with a Left Join which pulls back a currency amount for the rows.But I have now discovered that in some cases there may be rows duplicated in the database (based on 'asset' field) but this is how it is supposed to be. As a result I am trying to figure out a way of only assigning one currency amount per duplicated row and a zero amount for the extra duplications. So, currently with the below SQL, the asset 'Asset25' is returned twice and therefore summed as a total value of €10 (i.e. €5 + €5) when its value is really €5.How could I fix this?Thanks for any help...SELECT b.amount, * FROM database1 a LEFT JOIN database2 b on b.asset_id = a.asset |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2014-07-23 : 08:14:06
|
| Something like this may workselect case when sno=1 then amount else 0 end as amount, t.othercolumns from(SELECT row_number() over (partition by a.asset order by a.asset) as s_no,b.amount, * FROM database1 a LEFT JOIN database2 b on b.asset_id = a.asset) as tMadhivananFailing to plan is Planning to fail |
 |
|
|
kieran5405
Yak Posting Veteran
96 Posts |
Posted - 2014-07-23 : 08:29:30
|
| amazing work...thanks for that...would have never been able to put it together like that. |
|
|
|
|
|
|
Left Join issue with duplicate rows