return sum by quantity?

[yamipoli]

Really not sure what to call this, but lets assume we have this table called "InventoryTable"

--------------------------------------
| Item                      | Weight |
--------------------------------------
| Red Striped Wallpaper     | 125    |
| Green Striped Wallpaper   | 150    |
--------------------------------------

 

 

Then we have another table called "AmountLeft"

 

--------------------------------------
| Item                      | Qnty   |
--------------------------------------
| Red Striped Wallpaper     | 3      |
| Green Striped Wallpaper   | 4      |
--------------------------------------

 

What I want to ultimately do is return the total weight (which would be 3*125 + 4*150 = 975) based on the quantity of the item times the weight from the other table. Is there some way to get sum() to do calculations to multiple the weight by the quantity of each row?

[dragon_sa]

Is there any reason you dont have the quantity listed in the first table then there is no need for the second table at all?

[yamipoli]

Yes. The second table is how much each user has left (i neglected some fields, like user id etc, to make it easier to read)

 

EX user A has 3 Red Striped Wallpaper and 4 Green Striped Wallpaper and a total of 1000 storage space, so I wanted to total up the amount quicker than my current way of using php to do it.

[Muddy_Funster]

so youre looking for something like

SELECT SUM (inventoryTable.weight * AmountLeft.qnty) FROM AmountLeft INNER JOIN inventoryTable ON (amountLeft.Item = inventoryTable.Item) WHERE USER ID = <some id number>

Not that I think that will work, but does it look like what you want to do?

 

Could you also provide the full tables, as knowing all the filds and their relations makes things easier than just knowing what is apparently relevant.