Create an alias using a mathematical result from 2 other aliases

Question

I am trying to subtract 2 aliases in order to create another alias, but I am getting an "unknown column" error.

Here is my SQL:

select o.id, o.name, (select sum(l.source_expense) from `assignments` as a left join `leads` as l on (l.id = a.lead_id) where a.{$this->sql_column}=o.id and l.date_created between {$this->date_from} and {$this->date_to} and find_in_set(l.vertical_id, '".implode(',', $this->app_user->verticals)."') ) as `expense`, (select sum(a.buyer_revenue) from `assignments` as a left join `leads` as l on (l.id = a.lead_id) where a.refunded=0 and a.{$this->sql_column}=o.id and l.date_created between {$this->date_from} and {$this->date_to} and find_in_set(l.vertical_id, '".implode(',', $this->app_user->verticals)."') ) as `revenue`, `revenue` - `expense` as `profit` from {$this->sql_table} as o 

Basically, I would like to create an alias for profit by subtracting revenue from expense . The reason is because I use datatables and want the column to be sortable. I already know that I can easily do this with PHP.

How can i do this?

Change I tried to answer below and get the error message “Every view must have an alias” from PHPStorm and a syntax error when trying to execute a query.

A new query arises:

 select t.id, t.name, t.expense, t.revenue, t.revenue - t.expense as profit from(select o.id, o.name, (select sum(l.source_expense) from `assignments` as a left join `leads` as l on (l.id = a.lead_id) where a.{$this->sql_column}=o.id and l.date_created between {$this->date_from} and {$this->date_to} and find_in_set(l.vertical_id, '".implode(',', $this->app_user->verticals)."') ) as `expense`, (select sum(a.buyer_revenue) from `assignments` as a left join `leads` as l on (l.id = a.lead_id) where a.refunded=0 and a.{$this->sql_column}=o.id and l.date_created between {$this->date_from} and {$this->date_to} and find_in_set(l.vertical_id, '".implode(',', $this->app_user->verticals)."') ) as `revenue` from {$this->sql_table} as o ) as t 

Answer 1

Just wrap it with another choice, then the aliases will be available for using mathematical calculation:

 SELECT t.id,o.name,t.expense,t.revenue, t.revenue -t.expense as `profit` FROM (Your Query Here) t 

Answer 2

You need to put the request in a subquery.

 SELECT t.*, t.`revenue` - t.`expense` as `profit` FROM ( select o.id, o.name, (select sum(l.source_expense) from `assignments` as a left join `leads` as l on (l.id = a.lead_id) where a.{$this->sql_column}=o.id and l.date_created between {$this->date_from} and {$this->date_to} and find_in_set(l.vertical_id, '".implode(',', $this->app_user->verticals)."') ) as `expense`, (select sum(a.buyer_revenue) from `assignments` as a left join `leads` as l on (l.id = a.lead_id) where a.refunded=0 and a.{$this->sql_column}=o.id and l.date_created between {$this->date_from} and {$this->date_to} and find_in_set(l.vertical_id, '".implode(',', $this->app_user->verticals)."') ) as `revenue` from {$this->sql_table} as o ) AS t 

Note:

You can only use column aliases in GROUP BY, ORDER BY, or HAVING .

Standard SQL does not allow you to refer to a column alias in the WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not be defined yet.

Link