CODEWITHSUNDEEP
sqlvisual-studiocalculated-field
Angad
Angad

Reputation: 21

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.

Upvotes: 2

Views: 5928

Answers (4)

SQLMenace
SQLMenace

Reputation: 135151

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453807

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

Upvotes: 0

Adam Robinson
Adam Robinson

Reputation: 185693

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)

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425743

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

Upvotes: 4

Related Questions