SUM from a loop

cbjewelz

Hey all
I will explain what I am trying to do and then show you my code. I am running a looped mysql query on table1 in order to grab the id's of several table entries according to my WHERE statement. I then wish to use the id's i have retrieved in the WHERE statement of a second mysql query on table2 where i draw all values in a column where the id of table2 = id of table 1. So far everything works but I wish to SUM (im using the SUM statement in my table2 mysql query) all of these values from table2 and print the results. However due to the loop from the query on table 1 it is giving me the sum of each entry rather than of the column. here is the code:

//select the id from table1 with given condition
$getratingid = mysql_query("SELECT id from table1 where submittedbyuser = '$_SESSION[username]'"); 
$ratingnumid = mysql_num_rows($getratingid); 

//loop this query so that i grab all the ids that meet my condition
for ($counttwo = 0; $counttwo < ($ratingnumid) ; $counttwo++) {
$ratingid = mysql_fetch_array($getratingid);  

$locid = "".$ratingid['id']."h";


//use the ids from above to grab a query from table2
$getratingl = mysql_query("SELECT SUM(total_value) from table2 where id = '$locid'"); 
$ratingl = mysql_fetch_array($getratingl);

echo $ratingl['SUM(total_value)'];
}

for sake of simplicity table2 looks like this:
id | total_value
1h | 1
2h | 4
and so on

the results of the code gives me 1 and 4 rather than 5. maybe i need another loop not sure tho.
any help please?

Installer

Something more like this might work:

$query = "SELECT total_value, SUM(total_value) AS total_value_sum " .
         "FROM table2 WHERE id = '" . $locid . "'";

cbjewelz

hmm..didn't seem to do anything different

Installer

I'm not surprised. You'll need to play around with it some.

How are you echoing the results?

cbjewelz

heres wat ive got right now:

$getratingid = mysql_query("SELECT id from table1 where submittedbyuser = '$_SESSION[username]'"); 

while($ratingid = mysql_fetch_array($getratingid)) {  

$locid = $ratingid['id'];



$getratingl = mysql_query("SELECT SUM(total_value) as sum, SUM(total_votes) as sumtwo from ratings where id = '$locid'"); 
$ratingl = mysql_fetch_array($getratingl);


echo $ratingl[sum]/$ratingl[sumtwo]; //getting the rating here
}

this displays individual values from each loop where id = $locid.

ill give an equivalent example of what im tryin to do that is simpler:
imagine a database of people with id and ages in one table (table2) and locations and the same id value in another table (table1). what i want to do is draw out all the ages from table2 where the id's from each table match and then get the sum of all of those ages.

my code right now is the giving me the equivalent of each age. what i cant seem to do is get all of the values after the loop has finished and sum them

Installer

Well, just for now 🙂, I recommend a second query on table2 to get the sum.

cbjewelz

thats what im doin tho isnt it..ratings is the name of table2

harmor

I'm not an expert on how "left join" works as I'm new to it but try this query

 $getratingid = mysql_query("
 SELECT SUM(t2.total_value) AS sum, SUM(t2.total_votes) AS sumtwo 
 FROM table1 AS t1
 LEFT JOIN ratings AS t2
 ON t1.id = t2.id
 where submittedbyuser = '$_SESSION[username]'");
while($ratingl = mysql_fetch_array($getratingid)) 
{  

echo $ratingl[sum]/$ratingl[sumtwo];

}