Mysql - Search Into A Custom Column

I want to run a MySQL query like this- SELECT country_ID*2/id*3.159 as my_id FROM `state` WHERE my_id>2; When I run it, I am getting an error like this- 1054 - Unknown colu

Solution 1:

You cannot refer in WHERE to aliases, use instead:

SELECT country_ID*2/id*3.159as my_id 
FROM `state`
WHERE (country_ID*2/id*3.159)>2;

or use subquery:

SELECT t.*
FROM
(
   SELECT country_ID*2/id*3.159as my_id 
   FROM `state`
) as t
WHERE t.my_id>2

Simplified logical query processing, SELECT is almost last, so WHERE doesn't know about my_id alias:

Solution 2:

Use complete condition again in where clause as:

  DB::table(    'project')->select( 'project.id as id',
                                    'project.completion_date as completion_date',
                                     DB::raw('FORMAT(project.total_cost_to_dispose - project.actual_cost_dispose, 2) as disposal_savings')
                                   )
                            ->whereFORMAT(project.total_cost_to_dispose - project.actual_cost_dispose, 2) > 100;