I am using SQL Server 2008.
I am trying to do some basic math in some basic queries. I need to add up wins, losses, total, and percentages. I usually ask for the raw numbers and then do the calculations once I return my query to page. I would like to give SQL Server the opportunity to work a little harder.
What I want to do is something like this:
SELECT SUM(case when vote = 1 then 1 else 0 end) as TotalWins,
SUM(case when vote = 0 then 1 else 0 end) as TotalLosses,
TotalWins + TotalLosses as TotalPlays,
TotalPlays / TotalWins as PctWins
Here's what I am doing now:
SELECT SUM(case when vote = 1 then 1 else 0 end) as TotalWins,
SUM(case when vote = 0 then 1 else 0 end) as TotalLosses,
SUM(case when vote = 1 then 1 else 0 end) + SUM(case when vote = 0 then 1 else 0 end) as Votes
What is the easiest, cleanest way to do simple math calculations like this in a query?
*EDIT: *
While I got some great answers, I didn't get what I was looking for.
The scores that I will be calculating are for a specific team, so, my results need to be like this:
TeamID Team Wins Losses Totals
1 A's 5 3 8
2 Bee's 7 9 16
3 Seas 1 3 4
SELECT T.TeamID,
T.Team,
V.TotalWins,
V.TotalLosses,
V.PctWins
FROM Teams T
JOIN
SELECT V.TeamID,
SUM(case when vote = 1 then 1 else 0 end) as V.TotWin,
SUM(case when vote = 0 then 1 else 0 end) as V.TotLoss
FROM Votes V
GROUP BY V.TeamID
I tried a bunch of things, but don't quite know what wrong. I am sure the JOIN part is where the problem is though. How do I bring these two resultsets together?
One way is to wrap your query in an external one:
SELECT TotalWins,
TotalLosses,
TotalWins + TotalLosses as TotalPlays,
TotalPlays / TotalWins as PctWins
FROM
( SELECT SUM(case when vote = 1 then 1 else 0 end) as TotalWins,
SUM(case when vote = 0 then 1 else 0 end) as TotalLosses
FROM ...
)
Another way (suggested by @Mike Christensen) is to use Common Table Expressions (CTE):
; WITH Calculation AS
( SELECT SUM(case when vote = 1 then 1 else 0 end) as TotalWins,
SUM(case when vote = 0 then 1 else 0 end) as TotalLosses
FROM ...
)
SELECT TotalWins,
TotalLosses,
TotalWins + TotalLosses as TotalPlays,
TotalPlays / TotalWins as PctWins
FROM
Calculation
Sidenote: No idea if this would mean any preformance difference in SQL-Server but you can also write these sums:
SUM(case when vote = 1 then 1 else 0 end)
as counts:
COUNT(case when vote = 1 then 1 end) --- the ELSE NULL is implied
try
select a, b, a+b as total
from (
select
case ... end as a,
case ... end as b
from realtable
) t
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With