CODEWITHSUNDEEP
mysqlsql
Bubba Yakoza
Bubba Yakoza

Reputation: 799

How to subtract two calculated fields from the same table in MySQL?

SELECT   *, 
         SUM(price+shipping+paypalfee+storefee) AS totalcost, 
         customerpaid                           AS totalrevenue, 
         (totalcost - totalrevenue)             AS profit 
FROM     tblsales 
GROUP BY orderno 
HAVING   " . $having . " 
ORDER BY $sort $order 
LIMIT    $offset,$rows

If I omit (totalcost - totalrevenue) as profit the query works fine. How could I calculate PROFIT in the same query using totalcost and totalrevenue?

Upvotes: 3

Views: 13090

Answers (2)

Ajit Kumar
Ajit Kumar

Reputation: 345

You can do like this SELECT * , (totalcost - totalrevenue) AS profit FROM( SELECT *, SUM(price+shipping+paypalfee+storefee) AS totalcost, customerpaid AS totalrevenue,

FROM tblsales GROUP BY orderno HAVING " . $having . " ORDER BY $sort $order ) LIMIT $offset,$rows

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

The answer to your question is that you have to repeat the expressions:

select *, sum(price+shipping+paypalfee+storefee) as totalcost
       customerpaid as totalrevenue,
       (sum(price+shipping+paypalfee+storefee) - customerpaid) as profit
from tblsales
group by orderno
having " . $having . "
order by $sort $order
limit $offset, $rows;

You are not allowed to use a column alias in the same select where it is defined.

And, your query looks weird. Any query that has select * and group by is suspect. You have many columns (presumably) whose value will come from an indeterminate row for each group. You should explicitly list the columns in general, but you should especially do so for a group by.

Upvotes: 4

Related Questions