How to sum a generated column in MySQL?

Question

This query works correctly, it generates a column named "total_each". I just want to sum all the values ​​from this column and display them.

I think this is a fairly simple thing, but I can’t find a suitable way to write it, I have already looked everywhere.

select ((datediff(date1, date2))*daily_cost) as total_each
from table1, table2, table3 
where bid = fk1_bid 
AND vid = fk2_vid 
AND bid = 2;

I think in the bottom row I don't know how to apply the SUM function to the created column.

Answer 1

You call SUM in the column expression:

SELECT SUM((DATEDIFF(date1, date2))*daily_cost) AS total_each
  FROM table1, table2, table3 
 WHERE bid = fk1_bid 
   AND vid = fk2_vid 
   AND bid = 2;

I recommend using JOIN notation. We cannot determine exactly how to rewrite your query using JOIN, because we don’t know which column belongs to which table, but it might look like this:

SELECT SUM((DATEDIFF(t1.date1, t2.date2))*t3.daily_cost) AS total_each
  FROM table1 AS t1
  JOIN table2 AS t2 ON t1.bid = t2.fk1_bid
  JOIN table3 AS t3 ON t1.vid = t3.fk2_vid
 WHERE t1.bid = 2;

Answer 2

select sum(total_each) from (select ((datediff(date1, date2))*daily_cost) as total_each
from table1, table2, table3 
where bid = fk1_bid 
AND vid = fk2_vid 
AND bid = 2)