Use selected value as a parameter

Question

I'm writing a data explorer query to find the accepted answers with the lowest score, and this part works fine:

SELECT TOP 25 a.Id as [Post Link],
              a.Score as Score,
              (SELECT COUNT(*)
               FROM Votes
               WHERE PostId = a.Id AND
                     VoteTypeId = 2) AS [Upvotes],
              (SELECT COUNT(*)
               FROM Votes
               WHERE PostId = a.Id AND
                     VoteTypeId = 3) AS [Downvotes]
FROM Posts q INNER JOIN Posts a ON q.AcceptedAnswerId = a.Id
WHERE a.PostTypeId = 2
ORDER BY a.Score ASC

However, when I tried to work out what score the person who posted the answer has, I can't get it to accept the [Upvotes] and [Downvotes] columns I'm selecting as parameters, i.e.:

SELECT TOP 25 a.Id as [Post Link],
              a.Score as Score,
              (SELECT COUNT(*)
               FROM Votes
               WHERE PostId = a.Id AND
                     VoteTypeId = 2) AS [Upvotes],
              (SELECT COUNT(*)
               FROM Votes
               WHERE PostId = a.Id AND
                     VoteTypeId = 3) AS [Downvotes],
              (15 + (([Upvotes] * 10) - ([Downvotes] * 2))) AS [Answerer's Reputation]
FROM Posts q INNER JOIN Posts a ON q.AcceptedAnswerId = a.Id
WHERE a.PostTypeId = 2
ORDER BY a.Score ASC

I'm told [Upvotes] and [Downvotes] are not a valid column names:

Invalid column name 'Upvotes'. Invalid column name 'Downvotes'.

How do I indicate that [Upvotes] is the same value I've just declared: AS [Upvotes]? Or is this simply not possible and I'd have to do it manually as a stored procedure?

Answer 1

The problem is that the calculated columns are calculated at the same time as when you are referencing the columns, so SQL doesn't know about them.

This would be one solution

SELECT A.[Post Link], 
   A.Score, 
   A.Upvotes, 
   A.Downvotes, 
   (15 + (([Upvotes] * 10) - ([Downvotes] * 2)))AS [Answerer's Reputation]
FROM(SELECT TOP 25 P.Id AS [Post Link], 
                 P.Score AS Score, 
                 (
   SELECT COUNT(*)
     FROM Votes
    WHERE Postid = P.Id
      AND Votetypeid = 2)AS [Upvotes], 
                 (
   SELECT COUNT(*)
     FROM Votes
    WHERE Postid = P.Id
      AND Votetypeid = 3)AS [Downvotes]
     FROM Posts Q
          INNER JOIN Posts P ON Q.Acceptedanswerid = P.Id
    WHERE P.Posttypeid = 2)A
 ORDER BY A.Score ASC;