xteetsx xteetsx - 27 days ago 9
MySQL Question

multiplying two columns, displaying as new column in select statement sql

trying to take values from two columns, multiply them, and display the result as a new column in, all in a select statement. Below is what I got so far

select m.item_name, m.price, sum(od.qty) as count
from menu as m, order_detail as od, sum(m.price * od.qty) as tsales
where m.menuid = od.menuid
group by m.item_name
order by count desc
limit 10;


I can get the select statement to do everything BUT show the new column with the new values. Without the last part of the select statement it does this:

select statement result without extra column
error message

Answer

I think we are looking for a result that would be returned from a statement like this:

  SELECT m.item_name
       , SUM(m.price*od.qty) AS tot_price_times_qty
    FROM menu m
    JOIN order_detail od
      ON od.menuid = m.menuid
   GROUP BY m.item_name
   ORDER BY tot_price_times_qty DESC
   LIMIT 10 

But, without any sample data or demonstration of expected output, that's only a guess.