Using an Alias in SQL Calculations
Simply wrap your reused alias with (SELECT alias)
:
SELECT 10 AS my_num,
(SELECT my_num) * 5 AS another_number
FROM table
How to use an Alias in a Calculation for Another Field
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)
Reference alias (calculated in SELECT) in WHERE clause
You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated. Two workarounds:
SELECT BalanceDue FROM (
SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
) AS x
WHERE BalanceDue > 0;
Or just repeat the expression:
SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE (InvoiceTotal - PaymentTotal - CreditTotal) > 0;
I prefer the latter. If the expression is extremely complex (or costly to calculate) you should probably consider a computed column (and perhaps persisted) instead, especially if a lot of queries refer to this same expression.
PS your fears seem unfounded. In this simple example at least, SQL Server is smart enough to only perform the calculation once, even though you've referenced it twice. Go ahead and compare the plans; you'll see they're identical. If you have a more complex case where you see the expression evaluated multiple times, please post the more complex query and the plans.
Here are 5 example queries that all yield the exact same execution plan:
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE LEN(name) + column_id > 30;
SELECT x FROM (
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE column_id + LEN(name) > 30;
SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;
SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE LEN(name) + column_id > 30;
Resulting plan for all five queries:
SQL not using ALIAS column for calculation
ORA-00904: "TOTAL_TRIPS": invalid identifier
just means what is written "total_trips" is invalid
Just use total_trips (without quote)
Using calculation with an an aliased column in ORDER BY
The documentation clearly states that:
The column names referenced in the ORDER BY clause must correspond to
either a column or column alias in the select list or to a column
defined in a table specified in the FROM clause without any
ambiguities. If the ORDER BY clause references a column alias from
the select list, the column alias must be used standalone, and not as
a part of some expression in ORDER BY clause:
Technically speaking, your query should work since order by clause is logically evaluated after select clause and it should have access to all expressions declared in select clause. But without looking at having access to the SQL specs I cannot comment whether it is a limitation of SQL Server or the other RDBMS implementing it as a bonus feature.
Anyway, you can use CROSS APPLY
as a trick.... it is part of FROM
clause so the expressions should be available in all subsequent clauses:
SELECT item
FROM t
CROSS APPLY (SELECT item + '') AS CA(item_for_sort)
ORDER BY item_for_sort
How to use the alias of my calculated parameter (in SELECT clause) in WHERE clause with PostgreSQL to prevent repetition?
You can't reuse a select
alias in the where
clause. You need to repeat the expression, or use a subquery or cte.
For what it's worth, you can use between
to have the expression just once instead of twice in the where
clause:
SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
FROM public.foo f
WHERE date_part('year', CURRENT_DATE) - f.birth_year BETWEEN 20 AND 30
As for the subquery solution, that would be:
SELECT *
FROM (
SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
FROM public.foo
) f
WHERE age BETWEEN 20 AND 30
Related Topics
Convert Varchar into Datetime in SQL Server
Doing a Where .. in Subquery in Doctrine 2
Transpose Latest Rows Per User to Columns
Why Does MySQL Allow "Group By" Queries Without Aggregate Functions
Db2 Comma Separated Output by Groups
SQL Server 2005 How Create a Unique Constraint
MySQL, Better to Insert Null or Empty String
Dynamic Pivot Columns in SQL Server
How to Concatenate Columns in a Postgres Select
How to Install Freetds in Linux
MySQL Unknown Column in on Clause
Postgresql Group by Different from MySQL
MySQL: Invalid Use of Group Function
How to Use on Delete Cascade in MySQL
SQL to Json - Array of Objects to Array of Values in SQL 2016
How to Run a SQL Query on an Excel Table