Creating an alias using math result of 2 other aliases

Question

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

score 2 · Answer 1 · edited May 23 '17 at 12:22

You need to put your query inside 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 clauses.

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

Reference

I've used alias. Did you try the full query that I've posted? @kjdion84 — 1000111, Aug 28 '16 at 09:24
The refference and the quote you provided are talking about the `WHERE` clause, the OP asked about using the aliases inside the `SELECT` statement . — sagi, Aug 28 '16 at 11:14
I tried to focus on the scope of the usage of `alias`. There's nothing wrong with that (may be). It would have been better if I would also mention the execution order of sql statements. @sagi — 1000111, Aug 28 '16 at 12:00

score 1 · Accepted Answer · answered Aug 28 '16 at 07:56

1

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

answered Aug 28 '16 at 07:56

sagi

40,026
6
59
84

Same as other answer, getting error - "each derived table should have alias" – kjdion84 Aug 28 '16 at 08:39
@kjdion84 Post your full query, I'll tell you what you did wrong. – sagi Aug 28 '16 at 08:41
@kjdion84 You are selecting `O.` in the outer query instead of `T.Column>` , (id and name) , fix that and try again – sagi Aug 28 '16 at 08:54

Creating an alias using math result of 2 other aliases

2 Answers2