SQl calculations? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQl calculations?

Author

Topic

syphlix
Starting Member

3 Posts

Posted - 2011-04-06 : 16:04:51

Hello,

I have a table that has a list of "trades"

i.e.
symbol profit
XYZ $10
XYZ $7
XYZ $-6
ABC $4
ABC $5
ABC $6

How would I do a query that would calculate the win %?

So for this table, i'd like the result to be:
XYZ 66%
ABC 100%

Does that make sense? I tried doing a calculation in the query field of Access 2003 that does a count of the total # of trades, then divides the number of positive trades, but can't seem to make this not error out

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-04-06 : 16:54:23

[code]
select
a.symbol,
WinPct =
convert(decimal(6,2),round(100.0000*(sum(case when profit > 0 then 1.0000 else 0.0000 end)/ count(*)),2))
from
( -- Test Data
select symbol = 'XYZ', profit = 10 union all
select symbol = 'XYZ', profit = 7 union all
select symbol = 'XYZ', profit = -6 union all
select symbol = 'ABC', profit = 4 union all
select symbol = 'ABC', profit = 5 union all
select symbol = 'ABC', profit = 6
) a
group by
a.symbol
order by
a.symbol[/code]

Result:
[code]symbol WinPct
------ --------
ABC 100.00
XYZ 66.67 [/code]

CODO ERGO SUM

syphlix
Starting Member

3 Posts

Posted - 2011-04-25 : 15:17:15

does this make sense if the actual table is 300 rows?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-04-25 : 16:06:36

quote:
Originally posted by syphlix

does this make sense if the actual table is 300 rows?

How did it work when you tested it with 300 rows?

CODO ERGO SUM

syphlix
Starting Member

3 Posts

Posted - 2011-04-26 : 08:21:59

mmm... i misread the original reply...

though when i tried testing it i got "Syntax error (missing operator) in query expression 'WinPct = convert(decimal(6,2),round(100.0000*(sum(case when profit > 0 then 1.0000 else 0.0000 end)/ count(*)),2))',

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-04-26 : 22:17:17

The code I posted works fine; try it and see.

You have some other error in your code, but we can't help you with that, since you didn't post it.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources