How to use an Alias in a Calculation for Another Field

by

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)

More Related Contents:

  1. SQl CASE Statement
  2. How to calculate age in T-SQL with years, months, and days
  3. Convert multiple rows into one with comma as separator [duplicate]
  4. How to convert Seconds to HH:MM:SS using T-SQL
  5. T-SQL: Selecting rows to delete via joins
  6. Combining INSERT INTO and WITH/CTE
  7. T-SQL Pivot? Possibility of creating table columns from row values
  8. What is a “batch”, and why is GO used?
  9. Getting the minimum of two values in SQL
  10. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  11. T-SQL loop over query results
  12. How to insert default values in SQL table?
  13. How to Round a Time in T-SQL
  14. T-SQL CASE Clause: How to specify WHEN NULL
  15. t-sql get all dates between 2 dates [duplicate]
  16. Return a value if no rows are found in Microsoft tSQL
  17. Check if a varchar is a number (TSQL)
  18. How to pivot table with T-SQL?
  19. Using Aliases in Where Clause or an Alternative Option?
  20. Extract the first word of a string in a SQL Server query
  21. Testing for inequality in T-SQL
  22. Replace a newline in TSQL
  23. T-SQL – function with default parameters
  24. Search for one value in any column of any table inside a database
  25. Using a cursor with dynamic SQL in a stored procedure
  26. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  27. Convert unix epoch timestamp to TSQL datetime
  28. T-SQL Between Dates Confusion
  29. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
  30. How to parse string into date?

Leave a Comment