Using a Calculated field in SQL Query

I have a sql query in which i 开发者_运维问答have a calculated field which calculates the Contribution Margin. I get it to display and the math works fine. The problem i'm having is that i want to only display the records in which the Contribution Margin is lower than 0.25. I know you cant use column alias in the where clause. I was wondering what the best way to go about doing this would be. I'm also using Visual Studio for this.

---

SELECT  *
FROM    (
        SELECT  m.*,
                compute_margin(field1, field2) AS margin
        FROM    mytable m
        ) q
WHERE   margin < 0.25

---

You can't use the column alias (unless you use your original query as a subquery), but you can use the expression that you're using to define the calculated value.

For example, if your query is this now:

select
    contribution_amount,
    total_amount,
    contribution_amount / total_amount as contribution_margin

from records

You could do this:

select
    contribution_amount,
    total_amount,
    contribution_amount / total_amount as contribution_margin

from records

where contribution_amount / total_amount < 0.25

Or this:

select * from
(
    select
        contribution_amount,
        total_amount,
        contribution_amount / total_amount as contribution_margin

    from records
)
where contribution_margin < 0.25

(Personally I find the first version to be preferable, but both will likely perform the same)

---

You can either

• repeat the calculation in the where clause
• wrap the query in a table expression (CTE or derived table) and use the alias in the where clause
• assign the alias in a cross apply.

To give an example of the last approach

select doubled_schema_id,*
from sys.objects
cross apply (select schema_id*2 as doubled_schema_id) c
where doubled_schema_id= 2

---

two ways, either the solution that Quassnoi posted(you can also use a CTE which is similar)

or WHERE compute_margin(field1, field2) < 0.25