sum join and multiply fields in two tables

[kophey]

Hi i'm having some issues with getting my query correct. well i suppose saying some is understating how completely lost i am.

 

I have 1 table that lists percentages of chemicals used per gallon, and another table that lists sales of of gallons. what i want to do is sum the number of gallons sold by product id and then multiply that value against the actual product chemical fields. so lets say

 

table1

product_id

chemical1

chemical2

chemical3

 

table2

product_id

date_sold

gallons

lbs

 

SELECT *, SUM(lbs) AS lbs_total, SUM(gallons) AS gal_total FROM sold GROUP BY product_id

 

but from here i want to join it to table1 and multiply the chemical percentages so i can see how many gallons of each chemical has been sold broken down by product_id.

 

so gal_total * each chemical = total chemicals sold for that product_id

 

i hope i'm making enough sense here, i'm just so lost on this right now. attached is a look at the table structure so you know what i'm working with.

thanks for all your help.

 

[attachment deleted by admin]

[fenway]

chemical1, 2, 3 shouldn't be columns... they should be rows.