kjdion84 kjdion84 - 3 months ago 15
MySQL Question

Creating an alias using math result of 2 other aliases

I'm trying to subtract 2 aliases in order to create another alias but 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'd like to create a
profit
alias by subtracting
revenue
from
expense
. The reason is that I'm using datatables and want the column to be sortable. I already know I can easily do this with PHP.

How can I accomplish this?

Edit - I've attempted the answers below and am getting an "Each derived table should have alias" error from PHPStorm, and a syntax error when attempting to run the query.

Heres the new query:

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

Just wrap it with another select , then the aliases will be available for uses of mathematical calculation :

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