• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

MySQL how to use calculation in WHERE clause

i initally tried to do
SELECT     (long_equation_here_that_uses_fields_in_this_row) AS Mileage ...  
      ...   WHERE   Mileage < TblFieldMileage  ...

in order to select only the Rows (records)  where the calculated Mileage was less than the table's field content value for that row.

How can i accomplish this on the fly calculation PER ROW in order to use in a relational expression in the WHERE clause?
  • 2
  • 2
2 Solutions
Small adjustment.
You can use it in the where:

SELECT  *  from table1
WHERE TblFieldMileage > (long_equation_here_that_uses_fields_in_this_row)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot use the alias in the WHERE, only in the ORDER BY.
so, you have to repeat the expression....

unless you put a subquery:

  FROM ( 
SELECT   TblFieldMileage  ,   (long_equation_here_that_uses_fields_in_this_row) AS Mileage 
    FROM ...
  WHERE ...   
 ) as sub_query
WHER Mileage < TblFieldMileage  ...

Open in new window

willsherwoodAuthor Commented:
Thanks Angel, that's even better!   I did not know about subqueries.
Wish i could give you points, sorry for the timing overlap.
You can request the moderator attention, and ask for the redistribution of points, as you prefer.
willsherwoodAuthor Commented:
i requested attn 3/17  and no response, so i'm trying again now 3/19
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now