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 |
|
jrockfl
Posting Yak Master
223 Posts |
 Posted - 2004-11-19 : 16:46:40
|
| How can I return 12 and and 67.8? The third item is a credit..rep sales amtSP107 67.8 12SP107 4.99 1SP107 -4.99 1 |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2004-11-19 : 16:49:44
|
| [code]SELECT --rep, SUM(sales) as SalesTotal SUM(CASE WHEN sales < 0 THEN amt * -1 ELSE amt END) as AmtTotalFROM MyTable--GROUP BY rep;[/code] |
 |
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-11-19 : 16:55:46
|
| perfect! that worked, i learned something new....seems like you do just about anything with SQL.Thank you!! |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-11-19 : 16:59:59
|
| how can i add one more case? case when sales = 0 then amt = 0 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-19 : 17:03:04
|
SUM(CASE WHEN sales < 0 THEN amt * -1 WHEN sales=0 THEN 0 ELSE amt END) as AmtTotalGo with the flow & have fun! Else fight the flow  |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-11-19 : 21:20:59
|
| Thank you! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-19 : 21:36:16
|
| Here's a slightly shorter version that should also work:SELECT Sum(Sign(sales) * amt) AS AmtTotalThe Sign() function returns a -1, 0, or 1 for negative, zero, or positive values, respectively. Multiplying by this value would give the same effect as the full CASE expression does. |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-11-19 : 22:12:51
|
| I will try that solution too. Thank you. I didnt know SQL can do trig too! |
|
|
|
|
|
|
SQL Help...SUM