Need help working out SUM query

**Melter** · Apr 25, 2009 10:39

I'm trying to put together a table that displays total scores per player. Each player has 2 columns in the dbase which I want to add individually and display the results, then add the 2 columns' totals together to display an overall total score.

I don't often use mysql and php so struggle with anything more than a single basic * query on a page and not needed to do multiple queries before. So having done a general query that displays the data from these columns can I make use of that query to SUM the data or do I have to close it and do a fresh query or not close it but still do a separate query? Thanks.

Code:

<head>
<style type="text/css">
* {
      margin:0;
      padding:0;
}
#scoretable {
    width: 400px;
    padding: 0;
    margin:0 0 20px 0;
}
#totaltable {
      float:right;
    width: 400px;
    padding: 0;
    margin:0 0 20px 0;
}
.clear {clear:right;}
th {
    font: bold 10px Verdana, Arial, Times, Georgia, sans-serif;
    color: #4f6b72;
    border-right: 1px solid #C1DAD7;
    border-bottom: 1px solid #C1DAD7;
    border-top: 1px solid #C1DAD7;
    text-align: left;
    padding: 6px 6px 6px 12px;
    background-color:#CAE8EA;
}
th.center {
      text-align:center;
}
th.nobg {
    border-top: 0;
    border-left: 0;
    border-right: 1px solid #C1DAD7;
    background: none;
}
td {
    border-right: 1px solid #C1DAD7;
    border-bottom: 1px solid #C1DAD7;
    background: #fff;
    padding: 5px 5px 5px 5px;
    color: #4f6b72;
}
td.alt {
    background: #F5FAFA;
    color: #797268;
}
th.spec {
    border-left: 1px solid #C1DAD7;
    border-top: 0;
    background-color:#f5fafa;
    font: bold 8px Verdana, Arial, Times, Georgia, sans-serif;
      text-align:center;
}
th.specalt {
    border-left: 1px solid #C1DAD7;
    border-top: 0;
    background-color:#fff;
    font: bold 8px Verdana, Arial, Times, Georgia, sans-serif;
      text-align:center;
    color: #797268;
}
</style>
</head>
<body>
<?
$username="*******";
$password="*******";
$database="*******";

mysql_pconnect(localhost,$username,$password);
@mysql_select_db($database) or die(mysql_error());
$query="SELECT * FROM Scoring_Track";
$result=mysql_query($query)or die(mysql_error());

$num=mysql_numrows($result);

mysql_close();
?>

<table id="scoretable" cellspacing="0" summary="The Scoring Track">
<tr>
  <th scope="col" colspan="2" abbr="Gaming Scores" class="nobg">Gaming Scores</th>
  <th scope="col" colspan="2" abbr="Dylan" class="center">Dylan</th>
  <th scope="col" colspan="2" abbr="James" class="center">James</th>
  <th scope="col" colspan="2" abbr="Mark" class="center">Mark</th>
  <th scope="col" colspan="2" abbr="Shaun" class="center">Shaun</th>
  <th scope="col" colspan="2" abbr="Simon" class="center">Simon</th>
</tr>
<tr>
  <th scope="row" class="spec">Date</th>
  <th scope="row" class="spec">Game</th>
  <th scope="row" class="spec">PPs</th>
  <th scope="row" class="spec">VPs</th>
  <th scope="row" class="spec">PPs</th>
  <th scope="row" class="spec">VPs</th>
  <th scope="row" class="spec">PPs</th>
  <th scope="row" class="spec">VPs</th>
  <th scope="row" class="spec">PPs</th>
  <th scope="row" class="spec">VPs</th>
  <th scope="row" class="spec">PPs</th>
  <th scope="row" class="spec">VPs</th>
</tr>

<?
$i=0;
while ($i < $num) {

$date=mysql_result($result,$i,"date");
$game=mysql_result($result,$i,"game");
$dylanplayerp=mysql_result($result,$i,"dylanplayerp");
$dylanvictoryp=mysql_result($result,$i,"dylanvictoryp");
$dylantributep=mysql_result($result,$i,"dylantributep");
$jamesplayerp=mysql_result($result,$i,"jamesplayerp");
$jamesvictoryp=mysql_result($result,$i,"jamesvictoryp");
$jamestributep=mysql_result($result,$i,"jamestributep");
$markplayerp=mysql_result($result,$i,"markplayerp");
$markvictoryp=mysql_result($result,$i,"markvictoryp");
$marktributep=mysql_result($result,$i,"marktributep");
$shaunplayerp=mysql_result($result,$i,"shaunplayerp");
$shaunvictoryp=mysql_result($result,$i,"shaunvictoryp");
$shauntributep=mysql_result($result,$i,"shauntributep");
$simonplayerp=mysql_result($result,$i,"simonplayerp");
$simonvictoryp=mysql_result($result,$i,"simonvictoryp");
$simontributep=mysql_result($result,$i,"simontributep");
$dylanbadge=mysql_result($result,$i,"dylanbadge");
$jamesbadge=mysql_result($result,$i,"jamesbadge");
$markbadge=mysql_result($result,$i,"markbadge");
$shaunbadge=mysql_result($result,$i,"shaunbadge");
$simonbadge=mysql_result($result,$i,"simonbadge");
?>

<tr>
  <th scope="row" class="specalt"><? echo $date; ?></th>
  <th scope="row" class="specalt"><? echo $game; ?></th>
  <th scope="row" class="specalt"><? echo $dylanplayerp; ?></th>
  <th scope="row" class="specalt"><? echo $dylanvictoryp; ?></th>
  <th scope="row" class="specalt"><? echo $jamesplayerp; ?></th>
  <th scope="row" class="specalt"><? echo $jamesvictoryp; ?></th>
  <th scope="row" class="specalt"><? echo $markplayerp; ?></th>
  <th scope="row" class="specalt"><? echo $markvictoryp; ?></th>
  <th scope="row" class="specalt"><? echo $shaunplayerp; ?></th>
  <th scope="row" class="specalt"><? echo $shaunvictoryp; ?></th>
  <th scope="row" class="specalt"><? echo $simonplayerp; ?></th>
  <th scope="row" class="specalt"><? echo $simonvictoryp; ?></th>
</tr>

<? 
$i++;
}
echo "</table>";
?>
<table id="totaltable" cellspacing="0" summary="The Total Points">
<tr>
  <th scope="col" abbr="The Showdown" class="nobg">The Showdown</th>
  <th scope="col" colspan="2" abbr="Dylan">Dylan</th>
  <th scope="col" colspan="2" abbr="James">James</th>
  <th scope="col" colspan="2" abbr="Mark">Mark</th>
  <th scope="col" colspan="2" abbr="Shaun">Shaun</th>
  <th scope="col" colspan="2" abbr="Simon">Simon</th>
</tr>
<tr>
  <th scope="row" class="specalt">&nbsp;</th>
  <th scope="row" class="specalt">10</th>
  <th scope="row" class="specalt">6</th>
  <th scope="row" class="specalt">3</th>
  <th scope="row" class="specalt">3</th>
  <th scope="row" class="specalt">20</th>
  <th scope="row" class="specalt">22</th>
  <th scope="row" class="specalt">20</th>
  <th scope="row" class="specalt">26</th>
  <th scope="row" class="specalt">20</th>
  <th scope="row" class="specalt">27</th>
</tr>
<tr>
  <th scope="row" class="specalt">Total Points</th>
  <th scope="row" colspan="2" class="specalt">16</th>
  <th scope="row" colspan="2" class="specalt">6</th>
  <th scope="row" colspan="2" class="specalt">42</th>
  <th scope="row" colspan="2" class="specalt">46</th>
  <th scope="row" colspan="2" class="specalt">47</th>
</tr>
</table>
<div class="clear"></div>
<?
mysql_free_result($result);
?>
</body>

**oddz** · Apr 25, 2009 11:15

Not much can be said without seeing the structures for he tables your referring to and how they relate.

**r937** · Apr 25, 2009 11:16

first, you should add an ORDER BY clause to your query, otherwise your palyer scores might come back all jumbled

second, if you are going to display detail rows, then you would accumulate the totals in php while looping over the result set produced by the detail query

moving thread to php forum

**Melter** · Apr 26, 2009 02:01

Okay amended the OP to include all the code for the page. It's probably a bit long-winded and I'm sure there's a simpler way to do this but I'm still learning.

Anyway the first table on the page displays each player's individual scores. The second table is where I'm struggling. So for example in this table I need to SUM dylanplayerp and display the figure, SUM dylanvictoryp and display the figure, then add those two totals together to display a full total.

So I'm not sure if I need to make use of the initial query, amend it, or do another query and if so how to structure extra queries in the code.

Hope that makes more sense. Thanks.

**oddz** · Apr 26, 2009 03:17

By table I was referring to the create table statement. Not the the mark-up table.

**Melter** · Apr 26, 2009 05:15

Okay I don't have a create table statement because I pre-built the database before doing the code for the page. The column names are in the code if that's what you need to know. Also shown here:

Code:

$date=mysql_result($result,$i,"date");
$game=mysql_result($result,$i,"game");
$dylanplayerp=mysql_result($result,$i,"dylanplayerp");
$dylanvictoryp=mysql_result($result,$i,"dylanvictoryp");
$dylantributep=mysql_result($result,$i,"dylantributep");
$jamesplayerp=mysql_result($result,$i,"jamesplayerp");
$jamesvictoryp=mysql_result($result,$i,"jamesvictoryp");
$jamestributep=mysql_result($result,$i,"jamestributep");
$markplayerp=mysql_result($result,$i,"markplayerp");
$markvictoryp=mysql_result($result,$i,"markvictoryp");
$marktributep=mysql_result($result,$i,"marktributep");
$shaunplayerp=mysql_result($result,$i,"shaunplayerp");
$shaunvictoryp=mysql_result($result,$i,"shaunvictoryp");
$shauntributep=mysql_result($result,$i,"shauntributep");
$simonplayerp=mysql_result($result,$i,"simonplayerp");
$simonvictoryp=mysql_result($result,$i,"simonvictoryp");
$simontributep=mysql_result($result,$i,"simontributep");
$dylanbadge=mysql_result($result,$i,"dylanbadge");
$jamesbadge=mysql_result($result,$i,"jamesbadge");
$markbadge=mysql_result($result,$i,"markbadge");
$shaunbadge=mysql_result($result,$i,"shaunbadge");
$simonbadge=mysql_result($result,$i,"simonbadge");

**Melter** · Apr 26, 2009 07:45

Okay I've added more queries and this works up to a point. If I do a separate query for each SUM I can output the results fine but if I combine the query for each player as below it outputs the 1st SUM in the query twice. Where am I going wrong?

What I've done is probably overkill but it's the only way I can think with my ltd knowledge to get this to work.

The new queries:

Code:

$dquery="SELECT SUM(dylanplayerp) AS dpp,SUM(dylanvictoryp) AS dvp FROM Scoring_Track";
$dresult=mysql_query($dquery) or die(mysql_error());

$jquery="SELECT SUM(jamesplayerp) AS jpp,SUM(jamesvictoryp) AS jvp FROM Scoring_Track";
$jresult=mysql_query($jquery) or die(mysql_error());

$mquery="SELECT SUM(markplayerp) AS mpp,SUM(markvictoryp) AS mvp FROM Scoring_Track";
$mresult=mysql_query($mquery) or die(mysql_error());

$shquery="SELECT SUM(shaunplayerp) AS shpp,SUM(shaunvictoryp) AS shvp FROM Scoring_Track";
$shresult=mysql_query($shquery) or die(mysql_error());

$siquery="SELECT SUM(simonplayerp) AS sipp,SUM(simonvictoryp) AS sivp FROM Scoring_Track";
$siresult=mysql_query($siquery) or die(mysql_error());

$dpptotal=mysql_result($dresult,"dpp");
$dvptotal=mysql_result($dresult,"dvp");
$jpptotal=mysql_result($jresult,"jpp");
$jvptotal=mysql_result($jresult,"jvp");
$mpptotal=mysql_result($mresult,"mpp");
$mvptotal=mysql_result($mresult,"mvp");
$shpptotal=mysql_result($shresult,"shpp");
$shvptotal=mysql_result($shresult,"shvp");
$sipptotal=mysql_result($siresult,"sipp");
$sivptotal=mysql_result($siresult,"sivp");

...and where it outputs:

Code:

<tr>
  <th scope="row" class="specalt">&nbsp;</th>
  <th scope="row" class="specalt"><? echo $dpptotal; ?></th>
  <th scope="row" class="specalt"><? echo $dvptotal; ?></th>
  <th scope="row" class="specalt"><? echo $jpptotal; ?></th>
  <th scope="row" class="specalt"><? echo $jvptotal; ?></th>
  <th scope="row" class="specalt"><? echo $mpptotal; ?></th>
  <th scope="row" class="specalt"><? echo $mvptotal; ?></th>
  <th scope="row" class="specalt"><? echo $shpptotal; ?></th>
  <th scope="row" class="specalt"><? echo $shvptotal; ?></th>
  <th scope="row" class="specalt"><? echo $sipptotal; ?></th>
  <th scope="row" class="specalt"><? echo $sivptotal; ?></th>
</tr>

**r937** · Apr 26, 2009 08:04

first of all, you need not break down the different sums into separate queries, they can all be retrieved with one query

but more importantly, you do ~not~ need to do any extra query for the sums at all -- since your first query retrieves the entire contents of the table from the database, you can simply calculate the (running) sums with php as you loop over the query result set

i'd show you how to do it, but i don't do php

**Melter** · Apr 26, 2009 08:27

Okay that's what I thought I might be able to do but have no idea how to work this out in php so ended up doing it this way. If anyone can simplify the process for me that would be great. Thanks.

**Melter** · Apr 28, 2009 02:11

Okay so this has been suggested to me which seems to make sense but I'm getting an invalid argument error on the line in red. Any thoughts?

Code:

<?
$total = 0;
foreach($row['dylanplayerp'] as $c)
{
$dpptotal = $total + $c;
}
?>

Output is like this:

Code:

<tr>
<th class="specalt"><? echo $dpptotal; ?></th>
</tr>

User Tag List

Thread: Need help working out SUM query

Thread Tools

Display

Need help working out SUM query

Bookmarks

Bookmarks

Posting Permissions