title: How to use an Alias in a Calculation for Another Field
body: <p>Does anybody know if there is a way to replicate the method used in <a href="https://stackoverflow.com/questions/2077475/using-an-alias-in-sql-calculations">this question</a> of using the alias of a sub query to perform calculations on another field in t- SQL?</p>
  <p>I tried using the same syntax for the following query in MS SQL Express and got the error below:</p>
  <pre><code>DECLARE @PracticeID INT
  DECLARE @Date1 date
  DECLARE @Date2 date
  
  SET @PracticeID = 11015
  SET @Date1 = '2017-06-01'
  SET @Date2 = '2017-09-01'
  
  SELECT prtc.PracticeName ,COUNT(CASE WHEN udi.DevicePlatform = 'iOS' THEN 1 ELSE NULL END) iOSLogins,  
             COUNT(CASE WHEN udi.DevicePlatform = 'Android' THEN 1 ELSE NULL END) AndroidLogins,
            ( SELECT COUNT(*)
              FROM UserEvent UE
              WHERE UE.EventTypeID = 1 AND
                    UE.PracticeID = au.PracticeID AND
                    (UE.EventDate BETWEEN @Date1 and @Date2)
            ) TotalNumberLogins, 
            (SELECT TotalNumberofLogins) - ((SELECT iOSLogins) + (SELECT AndroidLogins )) DesktopLogins
      FROM UserDeviceInfo UDI JOIN
           AppUser AU ON udi.UserID = au.UserID JOIN
           Practice PRTC ON au.PracticeID = prtc.PracticeID 
      WHERE au.PracticeID = @PracticeID AND 
            (udi.Created BETWEEN @Date1 AND @Date2)
      GROUP BY prtc.PracticeName, au.PracticeID
  </code></pre>
  <blockquote>
  <p>Msg 207, Level 16, State 1, Line 17 Invalid column name
  'TotalNumberofLogins'. Msg 207, Level 16, State 1, Line 17 Invalid
  column name 'iOSLogins'. Msg 207, Level 16, State 1, Line 17 Invalid
  column name 'AndroidLogins'.</p>
  </blockquote>
  <p>Not that it would make a difference, but I did try putting the alias's in quotes and brackets to no avail.</p>
  <p>I did manage to get the desired result from another method by performing the calculations using the same values as variables instead of alias's and then inserting them into a table.</p>
  <p>That query is however, verbose and I would like to know if there is any way of replicating the behavior in the referenced question for future use.</p>
  <p>Thank you for any help you can provide.</p>