Math query within another query

[netfrugal]

Ok, lets see who can understand what I am saying.....

I have 2 tables to pull some data from: Reports, and Judges

The reports table simple holds the reports from a bunch of students, while seven judges grade each report on a scale from 1-7 in three categories. So, a report can get a high score of 21.

My dilemma is to pull all the reports and show them on one single page - while producing a total sum of each category(from the judges scores) and the total sum of all categories(by adding all categories together).

I have 4 columns to display:

Report Title, Interest Score, Content Score, Methodology Score, Total Score

The report title is in the reports table, while the scores are in the judges table.

With this I add all the Interest scores for one report and divide it by 7 (number of judges). I do the same thing with Content Score and Methodology score. I then add those 3 category scores together to get the total score for that report.

So now I am having a math issue while querying from two separate tables, but trying to show all reports in a simple while() statement, and including the scores too. I'm not being so successful at it.

<?
$sql = mysql_query("SELECT * FROM reports"); 
 while ($row = mysql_fetch_array($sql)) {
 ?>

      <tr>
        <td><?php echo $row['report_title']; ?></td>
        <td>Interest sum</td>
        <td>Methodology sum</td>
        <td>Content sum</td>
        <td>total SUM</td>
      </tr>

<? } ?>

As you can see, I don't know quite how to pull multiple rows from a different table whose matching column is reports_id.

Anyone understand what I'm trying to accomplish?

[NogDog]

Untested, of course (plus guessing at some of the column/table names):

$sql = <<<EOD
SELECT r.report_title, 
       SUM(j.interest) AS int_sum, 
       SUM(j.methodology) AS meth_sum,
       SUM(j.content) AS cont_sum,
       SUM(j.interest) + SUM(j.methodology) + SUM(j.content) AS total
  FROM reports r INNER JOIN judges j ON r.reports_id = j.reports_id
  GROUP BY r.reports_id
EOD;
$result = mysql_query($sql) or die("Query failed ($sql): ".mysql_error());
while($row = mysql_fetch_assoc($result))
{
   printf(
      "<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
      $row['report_title'],
      $row['int_sum'],
      $row['meth_sum'],
      $row['cont_sum'],
      $row['total']
   );
}

[netfrugal]

thanks for the help! And it worked just fine.

I'd like to take this:

SUM(j.interest) AS int_sum, 

and divide the Sum by 7. And I know this is easily done by adding /7.

but I need to add more decimal places to the end of the number result.

Is there a way to do this?

thanks again!

[NogDog]

I would just alter the printf() format string. For instance, if you want 2 decimal places:

while($row = mysql_fetch_assoc($result))
{
   printf(
      "<tr><td>%s</td><td>%.2f</td><td>%.2f</td><td>%.2f</td><td>%.2f</td></tr>\n",
      $row['report_title'],
      $row['int_sum'],
      $row['meth_sum'],
      $row['cont_sum'],
      $row['total']
   );
} 

[NogDog]

PS: If you want the average value for each score, just use AVG() instead of SUM() in the query.

[netfrugal]

That definately worked:

 AVG(j.interest) AS int_sum,
       AVG(j.methodology) AS meth_sum,
       AVG(j.content) AS cont_sum,
       AVG(j.interest) + AVG(j.methodology) + AVG(j.content) AS total

thanks a ton! I didn't know about AVG