Select Sum() from multiple tables

Hi guys... I need to bother you again... I googled and tried a lot but i can't get to a solution.

My tables are made up this way:

table 1:

name - country - pts_1st_competition - pts_2nd_competition - pts_3rd_competition - pts_4th_competition

table 2:

pts_5th_competition - name - country

I want to obtain the sum of the points of the five competitions grouped by name and country.

Using the code below i've been able to get the sum of the points of the four competitions contained in the first table:

$query = "SELECT name, country, SUM(pts_1st_competition) + SUM(pts_2nd_competition) + SUM(pts_3rd_competition) + SUM(pts_4th_competition) AS tot FROM jos_chronoforms_table1 WHERE ..... GROUP BY name, country ORDER BY tot DESC";
$result =   mysql_query($query);
while ($row = mysql_fetch_assoc($result)){
        echo "<tr> <td align='center'>". $i++ ."</td> <td>" .$row['name']. "</td> <td>".$row['country'] ."</td> <td align='center'>" .$row['tot']. "</td> </tr>"; 
}
?>

How can tell my query to add the points of the fifth competition contained in the second table? I tried to add
"+ SUM(pts_5th_competition)" before "AS tot" and ", jos_chronoforms_table2" after "FROM jos_chronoforms_table1" but with no success... It returns me an empty table... I really can't get this to work...
Hopin' in someone's help... Thanks in advance...

Federico

Hi federico85,

Please try this - not tested and may need debugging!

$db =& JFactory::getDBO();
$query = "
  SELECT a.`name`, a.`country`, (
      SUM(a.`pts_1st_competition`) + 
      SUM(a.`pts_2nd_competition`) + 
      SUM(a.`pts_3rd_competition`) + 
      SUM(a.`pts_4th_competition`) + 
      SUM(b.`pts_5th_competition`)) AS tot
    FROM `#__chronoforms_table1` AS a
      INNER JOIN `#__chronoforms_table2` AS b USING (`name`, `country`)
    GROUP BY `name`, `country` 
    ORDER BY `tot` DESC ;
";
$db->setQuery($query);
$data = $db->loadObjectList();
$i = 0;
foreach ( $data as $d ) {
  echo "<tr>
    <td align='center'>{$i++}</td>
    <td>{$d->name}</td>
    <td>{$d->country}</td>
    <td align='center'>{$d->tot}</td>
    </tr>";
}
?>

Bob

Hi Bob... thanks for your reply... I've tried but it doesn't work... It returns the empty form with no table...

Hi federico,

Then I think the next step is to go into PHPMyAdmin and debug the query.

Bob

Thank you Bob...
I've tried and it works but I've had to replace the field names like a.`name` AND a.`country` in `name` AND`country` (with no letters and point before the field name)... I don't know why but now it seems to work...

Now I've encountered another problem cause when I add WHERE clause it stops working. I've tried to put it after "USING ( `name` , `country` )"...

Hi federico85,

Sorry, the problem with the a & b was my mistake these two lines should be

    FROM `#__chronoforms_table1` AS a
      INNER JOIN `#__chronoforms_table2` AS b USING (`name`, `country`)

What's the WHERE clause you are trying to use?

Bob

The WHERE clause I want to insert is WHERE edizione_anno=$anno_impostato2... But now maybe i got it... I changed the query as follows and it works:

 SELECT  a.`name` , a.`country` , a.`edizione_anno` , (
      SUM(a.`pts_1st_competition`) + 
      SUM(a.`pts_2nd_competition`) + 
      SUM(a.`pts_3rd_competition`) + 
      SUM(a.`pts_4th_competition`) + 
      SUM(b.`pts_5th_competition`)) AS tot
    FROM `#__chronoforms_table1` AS a
      INNER JOIN `#__chronoforms_table2` AS b
USING (  `name` ,  `country` , `edizione_anno`) 
WHERE edizione_anno=$anno_impostato2
GROUP BY  `name` ,  `country` 
ORDER BY  `tot` DESC  ;

Now i guess I'd better check the sum to be really ok...

Thanks a lot Bob

Oh no...
The sum ain't effectively ok...
It adds to the first ranked 400 pts:

N. NAME COUNTRY TOT
1 APSP PICCOLO SPEDALE PIEVE TESINO 1575
2 APSP SUOR AGNESE CASTELLO TESINO 955
3 APSP SAN VALENTINO LEVICO TERME 70

The first ranked TOT should be 1175 instead of 1575.
Lookin' into the table I've discovered that there are 4 empty value in fields we are trying to sum... Could be this to cause this problem...

I've tried to debug sum per sum using phpmyadmin and I've realized that it added four times the value of the fifth competition.
I didn't need a "+ SUM(pts_5th_competition)" cause in the second table there must be only one record witch satisfies the WHERE clause and so I still don't know why it added four times points of the fifth competition but i've solved replacing "+ SUM(pts_5th_competition)" with "+ (pts_5th_competition)".

Thanks again Bob...

Hi federico85,

It could be that the INNER JOIN needs to be a LEFT JOIN (I get thoroughly confused with the different joins and usually end up with trial and error).

Bob

Ok thanks... I'll try..

Hi Bob,
i tried to use LEFT JOIN instead of INNER JOIN but it didn't change the result... if i use "+ SUM(pts_5th_competition)" it always adds four times the points of fifth competition (400)...
I checked into the table but there is only one record containing 100 in field 'pts_5th_competition'...