mysql query, reuse columnnames in query

Question

I have a mysql query, something like this:

SELECT users*100 as totalusers, totalusers*90 AS totalsalerys FROM table

As you can see I want to reuse the totalusers when calculating totalsalerys so I son't have to write the same code again. That dosesn't seem to work in mysql, is there another easy way to do this?

My query is just an example, change the "100" and "90" to some veeeery long formula and you might see what i mean..

Thanks!

Answer 1

I believe you would have to copy/paste the formula or use a subquery. The below would work in T-SQL, but I imagine it'd work in MySQL as well since it's pretty simple.

 SELECT 
     x.totalusers, x.totalusers*90 AS totalsalerys 
 FROM (
      SELECT users*100 as totalusers FROM table
 ) x

Answer 2

SELECT  (@r := users * 100) as totalusers,
        @r * 90 AS totalsalerys
FROM    table

You can also use a subquery as @Tom Ritter advices, but it's not friendly to ORDER BY ... LIMIT ... clause.

In MySQL, all results of the inner subquery will be fetched before applying any filters in the outer subquery.