tsql - How to use an Alias in a Calculation for Another Field

Question

Does anybody know if there is a way to replicate the method used in this question of using the alias of a sub query to perform calculations on another field in t- SQL?

I tried using the same syntax for the following query in MS SQL Express and got the error below:

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

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

Not that it would make a difference, but I did try putting the alias's in quotes and brackets to no avail.

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.

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.

Thank you for any help you can provide.

Answer 1

That method doesn't work in SQL Server. You can accomplish the same thing in a couple different ways:

1.) Use the code for each aliased column instead of the alias:

(SELECT COUNT(*)
 FROM UserEvent UE
 WHERE UE.EventTypeID = 1 
 AND UE.PracticeID = au.PracticeID 
 AND (UE.EventDate BETWEEN @Date1 and @Date2) 
- COUNT(CASE WHEN udi.DevicePlatform = 'iOS' THEN 1 ELSE NULL END)
+ COUNT(CASE WHEN udi.DevicePlatform = 'Android' THEN 1 ELSE NULL END) Desktop Logics

2.) Use a derived table to make the columns, then you can reference them by alias:

SELECT PracticeName, iOSLogins, AndroidLogins, TotalNumberLogins,
       (TotalNumberofLogins - (iOSLogins + AndroidLogins)) DesktopLogins
FROM (
       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, 
       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
    ) a --table alias

Edit: Table alias explained

In a simple query:

SELECT col1 FROM Table

You know the table reference for col1 is Table. (Table.Col1) You don't have to write it if it is the only col1, but you still know the table it is referencing.

In a simple derived table:

SELECT col1 FROM (SELECT col1 FROM Table)

The table reference for the inner column is still Table, but what about the outer? In this case, everything within the parentheses is your table, but in the above example that table is unnamed. SQL Server requires that you name/alias the table that you have created so you can reference it:

SELECT col1 FROM (SELECT col1 FROM Table) MyDerivedTable

...and now you have a table reference for your outer column:

SELECT MyDerivedTable.col1 FROM (SELECT col1 FROM Table) MyDerivedTable

You can also see a greater need for this once more tables are involved:

SELECT MyDerivedTable.col1
FROM (SELECT col1 FROM Table) MyDerivedTable
JOIN Table T on T.col1 = MyDerivedTable.col1

Edit 2: CTE option:

Another option is a common table expression or CTE:

with cteName as (
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, 
   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
)

SELECT PracticeName, iOSLogins, AndroidLogins, TotalNumberLogins,
       (TotalNumberofLogins - (iOSLogins + AndroidLogins)) DesktopLogins
FROM cteName

These can be pretty convenient because they create a clear separation between the outer and inner queries. Ultimately it does the same thing as inline derived tables, so choose whichever version is more readable to you. (props to xorcus for suggesting the CTE addition)