Logic for a query?

Discussion in 'PHP' started by amaze, Mar 17, 2009.

0
  1. #1
    Hi,

    I currently have the query as below. However I want to work out how many units I have sold. To do this I need to multiply the od.ingredientid with od.quantity on a row per row basis, then the total of that is the amount of units sold.

    Is this possible all within SQL?

    select od.fkingredientid, od.quantity
from dorderdetails od, dordergroups og
where og.pkordergroupid = od.fkordergroupid
and od.fkingredientid = 391
and og.deleted = 0
    Code (markup):
    Thanks in advance.
     
    amaze, Mar 17, 2009 IP
  2. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #2
    I don't really understand your database structure, but you can generally do something like this:

    SELECT (ingredient*quantity) AS totsold FROM....WHERE

    You do the math you want, enclose it in parenthesis, then ad 'AS' and then give it a name. I don't know if this will get you all the way where you want to go, because I suspect you need to summarize that quantity after you calculate it.
     
    plog, Mar 17, 2009 IP
  3. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #3
    You should use group by with roll up for the total amount. It's better if you provide table structure and required fields for the resultset
     
    koko5, Mar 17, 2009 IP