Referencing a Calculated Column in the Where Clause SQL

Use a calculated column in a where clause

Using Derived Columns in a predicate

You'll need to wrap the inner query in a derived table or CTE in order to be able to use derived columns in the WHERE clause (Also, note SUM() is specified just once, using the results of the multiplication):

SELECT x.Code, x.AccountNumber, x.Sales
FROM
(
  SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales 
  FROM [dbo].Customer c 
      LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
      LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
      LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
  GROUP BY p.Code, c.AccountNumber
) AS x
WHERE x.Sales > 100;

Repeating the Derived Column in a HAVING clause

As per @Jonny's comment, the other way is not to DRY up the calculated column, but to instead repeat the calculation. Use HAVING instead of WHERE after a GROUP BY has been applied.

SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales 
FROM [dbo].Customer c 
  LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
  LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
  LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber
HAVING SUM(p.UnitPrice * od.QtyShipped) > 100;

In either case, as per comments below, note that the calculated expression is SUM(p.UnitPrice * od.QtyShipped) and not SUM(p.UnitPrice) * SUM(od.QtyShipped).

Reuse calculated column in WHERE clause

There is no way to reuse the calculated field on the same level SELECT. You will need to nest it in order to use the alias.

SELECT field1
    , calc_field
FROM (
    SELECT field1
        , CONCAT (field2, field3) AS calc_field
    FROM MyTable
    ) tbl
WHERE calc_field LIKE 'A%'

This is because of the order in which clauses are executed in a SQL query. As you can see in the way the clauses are listed, the SELECT clause, where the alias is generated, is executed after the WHERE clause.

Thus, the alias is not "visible" in the WHERE clause, because the alias is generated after the WHERE is applied.

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:

Referencing a calculated column in the where clause SQL

In addition to Aaron's answer, you could use a common table expression:

;with cte_FreeDaysRemaining as
    (
        select
            frdFreedays - DateDiff(dd,conReceiptToStock,GetDate()) As FreeDaysRemaining
            --, more columns
        from yourtable
    )
    select
        FreeDaysRemaining
        --, more columns
    from cte_FreeDaysRemaining
    where FreeDaysRemaining <= @intFreeDays

Use a calculated field in the where clause

Logically, the select clause is one of the last parts of a query evaluated, so the aliases and derived columns are not available. (Except to order by, which logically happens last.)

Using a derived table is away around this:

select * 
from (SELECT a, b, a+b as TOTAL FROM ( 
           select 7 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 8 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 0 as a, 0 as b FROM DUAL) 
    )
WHERE TOTAL <> 0 
; 

How can I reuse a calculated CASE column in the WHERE clause?

In SQL Server you are unable to use a calculated column in the WHERE clause, only in the ORDER BY clause.

So you either need a sub-query of some form or you have to repeat the calculation. CROSS APPLY is a neat way to accomplish this.

SELECT T1.id, X.AmberRed
FROM db.tbl1 T1
CROSS APPLY (VALUES (
    CASE WHEN T1.id LIKE 'A_SEQ%' THEN 'amber'
    WHEN TestReason = 'itf' THEN 'red'
    ELSE NULL END
)) AS X (AmberRed)
WHERE X.AmberRed IS NOT NULL;

But a simple sub-query will also do the job

SELECT X.id, X.AmberRed
FROM (
    SELECT T1.id
        , CASE WHEN T1.id LIKE 'A_SEQ%' THEN 'amber'
        WHEN TestReason = 'itf' THEN 'red'
        ELSE NULL END
    FROM db.tbl1 T1
) X
WHERE X.AmberRed IS NOT NULL;

Or you can even just repeat the expression if its simple:

SELECT T1.id
    , CASE WHEN T1.id LIKE 'A_SEQ%' THEN 'amber'
    WHEN TestReason = 'itf' THEN 'red'
    ELSE NULL END
FROM db.tbl1 T1
WHERE CASE WHEN T1.id LIKE 'A_SEQ%' THEN 'amber'
    WHEN TestReason = 'itf' THEN 'red'
    ELSE NULL END IS NOT NULL;

SQL Server Reference a Calculated Column

No.

All the results of a single row from a select are atomic. That is, you can view them all as if they occur in parallel and cannot depend on each other.

If you're referring to computed columns, then you need to update the formula's input for the result to change during a select.

Think of computed columns as macros or mini-views which inject a little calculation whenever you call them.

For example, these columns will be identical, always:

-- assume that 'Calc' is a computed column equal to Salaray*.25
SELECT Calc, Salary*.25 Calc2 FROM YourTable

Also keep in mind that the persisted option doesn't change any of this. It keeps the value around which is nice for indexing, but the atomicity doesn't change.

Calculated column in where-clause - performance

You can use HAVING to filter on a computed column:

SELECT a,b,c,(a*b+c) AS d, n FROM table
HAVING d > n
ORDER by d

Note that you need to include n in the SELECT clause for this to work.

Oracle SQL Calculated Column - How do I reference a calculated column in another calculation in the same query?

In other Stack Overflow questions/responses, CROSS APPLY has come up. This is not working for me, since I have 10-20 calculated columns I need to create (all of which reference the other calculated columns).

CROSS APPLY could be chained:

SELECT t.*, s1.val1, s2.val2, s3.val3
FROM tab t
CROSS APPLY (SELECT t.val1 * t.val2 AS val3 FROM dual) s1
CROSS APPLY (SELECT s1.val3 * 100   AS val4 FROM dual) s2
CROSS APPLY (SELECT s2.val4 * 1000  AS val5 FROM dual) s3
--...

All you need is to make sure that you are referencing objects in correct order.

Related:

How to use a calculated column to calculate another column in the same view

---

Related Topics