How to sum a generated column in MySQL?

Question

This query is working correctly, it generates a column called "total_each" I just want to sum all the values from that column and display them.

I believe its a pretty simple thing but i can't find the appropriate way of writing it, I 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 the bottom line is I don't know how to apply the SUM function for the generated column.

Answer 1

You invoke the SUM on 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 that you use the JOIN notation. We can't tell exactly how to rewrite your query using JOIN because we don't know which column belongs to which table, but it might look like:

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)