CODEWITHSUNDEEP
mysqlselectalias
kjdion84
kjdion84

Reputation: 10044

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

Upvotes: 5

Views: 224

Answers (2)

1000111
1000111

Reputation: 13519

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

Upvotes: 2

sagi
sagi

Reputation: 40481

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

Upvotes: 1

Related Questions