SUM Columns from 2 mysql tables

[bwyant32]

I have looked all over the net on how to fix this... I am creating a baseball statistical website and I have 2 tables with identical columns (2012hitting and 2013hitting). I am trying to create a page where I can 1) group each year's statistics (which I have been able to do) and 2) have a row named "Career Totals". Each table has about 15 columns, various stats. How can I add the data from my 2012hitting table to my 2013hitting table into a Career Totals row for each player?

 

This is what I'm trying now:

 

/* CONNECTION VARIABLES */

$id = $_GET['id']; // get var from URL


/* Get data. */
$sql = "SELECT * (sum(2012hitting.hr) +sum(2013hitting.hr)) as totalhr FROM 2012hitting, 2013hitting WHERE id='$id'";
$result = mysql_query($sql);

?>	


	<?php
$alternate = "2";
while ($row = mysql_fetch_array($result)) {
$field1 = $row["season"];
$field2 = $row["team"];
$field3 = $row["games"];
$field4 = $row["ave"];
$field5 = $row["slg"];
$field6 = $row["r"];
$field7 = $row["h"];
$field8 = $row["rbi"];
$field9 = $row["bb"];
$field10 = $row["k"];
$field11 = $row["hr"];
$field12 = $row["dbl"];
$field13 = $row["tpl"];
$field14 = $row["sb"];
$field15 = $row["obp"];
$field16 = $row["ops"];
if ($alternate == "1") {
$color = "#ffffff";
$alternate = "2";
}
else {
$color = "#E4E4E4";
$alternate = "1";
}
echo "<tr bgcolor=$color><td align='center'>$field1</td><td align='center'>$field2</td><td align='center'>$field3</td><td align='center'>$field4</td><td align='center'>$field5</td><td align='center'>$field6</td><td align='center'>$field7</td><td align='center'>$field8</td><td align='center'>$field9</td><td align='center'>$field10</td><td align='center'>$field11</td><td align='center'>$field12</td><td align='center'>$field13</td><td align='center'>$field14</td><td align='center'>$field15</td><td align='center'>$field16</td></tr>";
}
echo "</table>";
?>

 

Thank you in advance!

[Psycho]

Why do  you have two tables. Just have ONE table with another field to determine the date of the statistics. Makes much more sense. An application shouldn't require that the database be changed every year to keep working.

 

But, if you still chose to use the flawed approach, you can simply add the values in the PHP logic once you have the totals from the two tables. Or, if you want to do it in a query you will have to use subqueries which are very inefficient and could lead to performance problems down the road. I'd try to create the query, but looking at the field list I don't understand the structure of your tables and how it should be done.

 

For example, you have fields for "season", "team" and "games" along with the stats. But, players routinely change teams during a season. It doesn't appear you are creating multiple records for each player based upon different teams. I'm thinking the DB needs some work.

[marcbraulio]

Here is an inefficient solution, but it is still a solution:

 

<?php
$conn = new PDO('mysql:host=localhost;dbname=test', 'root', 'password');

$sql = "SELECT hr FROM 2012hitting
            UNION
            SELECT hr FROM 2013hitting";

$results = $conn->query($sql);

while ($row = $results->fetchObject()){
	$hr_array[] = $row->hr;
}

$total_hr = $hr_array[0] + $hr_array[1];
echo $total_hr;

 

Note: this is a PDO connection as oppose to the the regular mysql connection you are using. The concept is still the same.

 

If you decide not to change your table structure like "Psycho" suggested, which I highly recommend you do as it will make things much easier, look into the UNION and JOIN methods for SQL to make things work with the structure you currently have.