MySQL query to get column based on dynamic data

Question

How can I get the value of "profit" to be returned by MySQL. totalCost and totalSell are obtained from the database.

select 
  sum(cost) as totalCost
  , sum(sell) as totalSell
  , (totalSell-totalCost) as profit 
from orders

Himanshu · Accepted Answer · 2013-04-19 04:13:20Z

4

Why don't you try to directly count profit instead of using aliases?

Try this:

Select SUM(cost) AS totalCost
     , SUM(sell) AS totalSell
     , (SUM(sell) - SUM(cost)) AS profit
FROM orders

answered Apr 19, 2013 at 4:07

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PSR · Accepted Answer · 2013-04-19 04:07:57Z

1

Dont perform, operations on aliases. You need to change (totalSell-totalCost) to (sum(cost)-sum(sell))

 Select sum(cost) as totalCost  , sum(sell) as totalSell, (sum(cost)-sum(sell)) as profit
     from orders

answered Apr 19, 2013 at 4:07

PSR

40.4k43 gold badges115 silver badges154 bronze badges

PinnyM · Accepted Answer · 2013-04-19 04:30:22Z

1

You can't use aliases in the select portion of the query that is declaring them:

select sum(cost) as totalCost, 
       sum(sell) as totalSell, 
       sum(sell) - sum(cost) as profit
from orders

If you wanted to do this using an alias (for some reason), you can do so when defining them in a subquery:

select *, totalCost - totalSell as profit
from ( select sum(cost) as totalCost, 
              sum(sell) as totalSell, 
       from orders ) t

answered Apr 19, 2013 at 4:07

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges