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 |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-07 : 05:01:52
|
Hey Guys really quick random question i am having problems doing a subtraction and i don’t know why. This is my query SELECT [FDMSAccountNo],SUM([Tot_Purch_Amt]) as GrossSales,SUM([Refund_Amt])as SalesRefunds,sum([Tot_Purch_Amt])-sum([Refund_Amt]) as SaleNet,SUM([DCC_Purch_Amt])as DCC,SUM([DCC_Refund_Amt])as DCCRefunds,SUM([DCC_Purch_Amt])-SUM([DCC_Refund_Amt]) as DCCnet,SUM([Adjs]) as adjustments FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]group by FDMSAccountNoi want to get my grosssales column – Salesrefund to produce a netfigure At present my query is giving me an inaccurate figure for eg gross sales Salesrefund SaleNet2484691.81 -32893.84 2517585.65when in fact the salesnet should be 2451797.97Any ideas why ? |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-07 : 05:08:35
|
Can you at least please post the data types? Too old to Rock'n'Roll too young to die. |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-07 : 05:19:21
|
Hi Webfred What do you mean by data types ? string, varchar etc ? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-11-07 : 05:23:21
|
Yes.The column Tot_Purch_Amt and the other involved columns - they have a data type we need to know. Too old to Rock'n'Roll too young to die. |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-07 : 05:30:48
|
Hi webfred i am unable to find that out at present :( as i dont have rights to the table. :( |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-07 : 06:21:30
|
quote: At present my query is giving me an inaccurate figure for eg gross sales Salesrefund SaleNet2484691.81 -32893.84 2517585.65when in fact the salesnet should be 2451797.97
Do this, u will get 2451797.97sum([Tot_Purch_Amt])+sum([Refund_Amt]) as SaleNet--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-07 : 08:03:10
|
HI bandi I had to do this Sum([Tot_Purch_Amt])-sum(-[Refund_Amt]) as SaleNet, this returned the correct value |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-07 : 08:07:36
|
quote: Originally posted by masond HI bandi I had to do this Sum([Tot_Purch_Amt])-sum(-[Refund_Amt]) as SaleNet, this returned the correct value
okey that is also fine. Why i suggested + means i thought u had Refund_Amt with negative values...............--Chandu |
|
|
|
|
|