SUM query

[lukep11a]

Hi, I am trying to get the sum of two queries and echo the result, both $hometotal and $awaytotal work on their own but I don't know how to put them together or whether it can be put into one query. I have tried a few different variations but keep getting SQL syntax errors. Does anybody know how I can write this code? Thanks in advance

 

<?php
	$hometotal = "SELECT SUM(test_results.hts) FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.hometeam";
	$awaytotal = "SELECT SUM(test_results.ats) FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.awayteam";
	$total = $hometotal + $awaytotal;
	$result = mysql_query($total) or die(mysql_error());

	$row = mysql_fetch_assoc($result);

                echo $total;
                ?>

[gizmola]

You need to:

 

-issue the query (mysql_query)

-fetch the results (mysql_fetch_)

 

Fetching will get you a php variable with the row, and since you are using SUM() you should only get one row per query.

 

So, quite simply you need to do query1 first, then query2, and add the vairables as the last step.

[Psycho]

If you don't need the two separate totals and just the combined total, you should just need one query

SELECT SUM(test_results.hts)

FROM test_teams, test_results

WHERE test_teams.team = '$team'
  AND (test_teams.teamid = test_results.hometeam
       OR test_teams.teamid = test_results.awaytea)

[lukep11a]

If you don't need the two separate totals and just the combined total, you should just need one query

Code: [select]

 

SELECT SUM(test_results.hts)

 

FROM test_teams, test_results

 

WHERE test_teams.team = '$team'

  AND (test_teams.teamid = test_results.hometeam

      OR test_teams.teamid = test_results.awaytea)

 

 

But I need to get the SUM of both SUM(test_results.hts) and SUM(test_results.ats)

[gizmola]

He's doing a sum on 2 different columns though.

[lukep11a]

You need to:

 

-issue the query (mysql_query)

-fetch the results (mysql_fetch_)

 

Fetching will get you a php variable with the row, and since you are using SUM() you should only get one row per query.

 

So, quite simply you need to do query1 first, then query2, and add the vairables as the last step.

 

I got this from what you said but something is not quite right, it's not giving me any errors now but the result is 0 when it should be 9...

 

<?php
	$hometotal = "SELECT SUM(test_results.hts) FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.hometeam";
	$result = mysql_query($hometotal) or die(mysql_error());
	$row = mysql_fetch_assoc($result);

	$awaytotal = "SELECT SUM(test_results.ats) FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.awayteam";
	$result = mysql_query($awaytotal) or die(mysql_error());
	$row = mysql_fetch_assoc($result);

	$total = $hometotal + $awaytotal;
	?>
        <td width="100"><?php echo $total; ?></td>

[Psycho]

He's doing a sum on 2 different columns though.

 

Ah. I missed that.

 

Try this:

$query = "SELECT SUM(test_results.hts)
          FROM test_teams, test_results
          WHERE test_teams.team = '$team'
            AND test_teams.teamid = test_results.hometeam";
$result = mysql_query($hometotal) or die(mysql_error());
$hometotal = mysql_result($result, 0);

$query = "SELECT SUM(test_results.ats)
          FROM test_teams, test_results
          WHERE test_teams.team = '$team'
            AND test_teams.teamid = test_results.awayteam";
$result = mysql_query($awaytotal) or die(mysql_error());
$awaytotal = mysql_result($result, 0);

$total = $hometotal + $awaytotal;

 

Although, I'm curious why you are not using the team ID to begin with.

[gizmola]

You need to store the total variables somewhere.  Here's what I would do:

 

	$hometotal = "SELECT SUM(test_results.hts) AS homesum FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.hometeam";
	$result = mysql_query($hometotal) or die(mysql_error());
	$homerow = mysql_fetch_assoc($result);
            		
	$awaytotal = "SELECT SUM(test_results.ats) as awaysum FROM test_teams, test_results WHERE test_teams.team = '$team' AND test_teams.teamid = test_results.awayteam";
	$result = mysql_query($awaytotal) or die(mysql_error());
	$awayrow = mysql_fetch_assoc($result);

	$total = $homerow['homesum'] + $awayrow['awaysum'];
	?>

 

 

[gizmola]

Mjdamto's way and mine are roughly equivalent -- just using different fetching.  You probably could combine these in a single query as well as he originally suggested, which would be the ideal situation, but I figured that since your main issue was needing to understand the query -> fetch system better, I'd just stick to that.

[lukep11a]

Thankyou both for your help, it now works, went with gizmola's in the end because the other one was coming up with an error sayin query is empty! Thankyou both anyway though, really appreciate your help. Yes ideally though it would be one query but I'm just happy it's working. The reason I wasn't using teamid to begin with is because the page this code was going in was a link on each team name from the overall team table so had to use a $_GET on the team name, if that makes sense.

[Psycho]

The reason I wasn't using teamid to begin with is because the page this code was going in was a link on each team name from the overall team table so had to use a $_GET on the team name, if that makes sense.

 

I would assume you are dynamically creating the links from the results of a database query. Then you should be passing the ID in the URL - not the name.