Adding up values of numerous columns

[dc_jt]

Hi I have a product table containing:

 

product_id

product

price

 

I then have data such as

 

product_id = 1

product = test

price = 10

 

product_id = 2

product = test2

price = 20

 

product_id = 3

product = test3

price = 30

 

Then I have a survey results table with the following:

 

  survey_results_id

surveyor_id 

survey_date 

job_id 

room_id 

location_id 

elevation_id 

date_posted 

 

So job_id 123 had each product, how would i add these up.

 

I would have to select all products from product table and relate this to the product_id in the survey results table??

 

 

[Vikas Jayna]

In which table is the relation between job_id and product_id defined? Its not there in the above two tables.

[dc_jt]

Sorry I have product_id in the 2nd table also (the survey_results table)

[dc_jt]

Anyone?

[Barand]

What do you want to add, a count of the number of products or the total price of those products for each job?

[dc_jt]

Sorry didnt really make myself clear

 

I wanted to add the total of the type of each product depending on the job_id

 

I done it using this anyway if anyone would like to know:

 

	 public function GetStorageTotal($iJobsId)
    {
    $sSql = "SELECT sum(p.load) AS storage_load from survey_results sr
    INNER JOIN product p ON p.product_id = sr.product_id
    WHERE p.type ='storage' &&
    sr.job_id = '$iJobsId'";

    $load = mysql_fetch_object(mysql_query($sSql, $db));
    
    return $load->storage_load;
    } 

 

Thanks anyway