Geek Answers Handbook

How to add and split columns with an alias in a query?

I am using SQL Server 2008.

I am trying to do some basic math in some basic queries. I need to add winnings, losses, total and percent. I usually request raw numbers, and then do the calculations when I return my request to the page. I would like to give SQL Server the ability to work a little harder.

I want to do 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 is 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 great answers, I didn't get what I was looking for.

The ratings that I will calculate are for a specific team, so my results should be as follows:

TeamID   Team    Wins   Losses  Totals
1        A     5      3       8
2        Bee   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

, , . , JOIN - . ?

+5
4

, :

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 ...
)

( @Mike Christensen) - (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: , SQL-, :

SUM(case when vote = 1 then 1 else 0 end)

:

COUNT(case when vote = 1 then 1 end)    --- the ELSE NULL is implied
+9

select a, b, a+b as total
from (
  select
    case ... end as a,
    case ... end as b
  from realtable
) t
+4

, , :

SELECT   
    T.TeamID,
    T.Team,
    V.TotalWins,
    V.TotalLosses,
    PctWins  =  V.TotalWins * 100 / CAST(V.TotalWins + V.TotalLosses AS float)
FROM Teams T 
JOIN  (
    SELECT  
        TeamID,
        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 Votes
    GROUP BY TeamID
    ) as V on T.TeamID = V.TeamID

.

+3

, , ...

CREATE VIEW [Totals]
SELECT   
    SUM(case when T.vote = 1 then 1 else 0 end) as TotalWins,
    SUM(case when T.vote = 0 then 1 else 0 end) as TotalLosses,
    T.SomeGroupColumn
FROM SomeTable T
GROUP BY T.SomeGroupColumn
+2

More articles:


All Articles