title: How to add a aggregate function where selected columns are within the same query
body: <p>I want to include this following line of code to perform a calculation:</p>
  
  <pre><code>SUM((InitialTeamWeight / 10) * Form + InitialTeamWeight) AS FinalTeamWeight
  </code></pre>
  
  <p>But issue is that I can't call on the columns that are within the same SELECT Statement. I tried adding a sub query above this query that selects the above calculation but this performs a calculation for all the teams not individual teams.</p>
  
  <p>What do I need to do in order to add the calculation into the query without having the issue of undefined columns?</p>
  
  <p>Below is the current query:</p>
  
  <pre><code>SELECT TeamID,
      CASE WHEN TeamID = 0 THEN 0 
      ELSE SUM(po.playerWeighting)
      END TeamWeight
      --ABS(CHECKSUM(NewID())) % 10 + 1 AS Form
  -- cte    
  FROM(
          SELECT pl.*,
                 ROW_NUMBER() OVER(PARTITION BY pl.TeamID, pl.Position ORDER BY NEWID()) AS Rnk
  -- cte        
          FROM(
              SELECT DISTINCT p.PlayerID, p.Position, p.PlayerWeighting, p.FirstName, p.Surname, t.TeamID, t.TeamAbbreviation, f.WeekNumber
              FROM dbo.Fixture f
              INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID
              INNER JOIN dbo.Team t ON l.LeagueID = t.LeagueID
              INNER JOIN dbo.Player p ON t.TeamID = p.TeamID
              WHERE f.WeekNumber = 1) 
              pl) po 
      WHERE (po.Position = 'GK' and po.rnk = 1) OR 
            (po.Position = 'DF' and po.rnk &lt;= 4) OR
            (po.Position = 'MF' and po.rnk &lt;= 4) OR
            (po.Position = 'FW' and po.rnk &lt;= 2)
            GROUP BY TeamID
  </code></pre>