Query help please?

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 in 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

 

Just do it in the query itself, ie:

select a, b, a * b as product
from table

 

Ahhh I didn't know you can do that!

I now have:

select (od.quantity * od.amount) AS totalQTY
from dorderdetails od, dordergroups og
where og.pkordergroupid = od.fkordergroupid
and od.fkingredientid = 391
and og.deleted = 0

Code (markup):

What is the syntax to get the sum of totalQTY? Is that possible?

Thanks

 

Not sure. Maybe:

select a, b, a * b as product,
(select sum(a*b) from table) as total
from table

Subselect seems inefficient. Also, you might check out using query of query. I use that all the time to operate on queries that have been returned.

Anyone?