1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 2:41 am Apr 29, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Doing a SUM from a general query

         

Melter

4:36 pm on Apr 26, 2009 (gmt 0)

10+ Year Member



Okay I've done a general query call:
$query="SELECT * FROM Scoring_Track ORDER BY id";
I've used this query to display data from various columns. Now I need to do a SUM total on one column e.g. Player1Scores and I can't work out how I do this in php from the initial query.

The only way I can get it to work is to do an additional query for the SUM and as I need to total a few columns this is not an ideal situation.

Any help is gratefully appreciated.

midtempo

4:52 pm on Apr 26, 2009 (gmt 0)

10+ Year Member



why don't you do it in the same query?

SELECT *, sum(Player1Scores) as sumScores FROM Scoring_Track ORDER by id

?

Pico_Train

4:55 pm on Apr 26, 2009 (gmt 0)

10+ Year Member



This is one way:
After you've run you're query:
$total = 0;
foreach($column as $c){
$total = $total + $c}

echo $total;

that 'should' give you the total of the columns you specify as $column.

This is another. I have no idea if this will work but it might also be useful:

$query="SELECT *, sum(column_to_be_summed) as sum_column FROM Scoring_Track ORDER BY id";

Does that help?

Pico_Train

5:03 pm on Apr 26, 2009 (gmt 0)

10+ Year Member



I answered while midtempo was doing his. Try that! Should do the trick!

Melter

5:10 pm on Apr 26, 2009 (gmt 0)

10+ Year Member



$query="SELECT *, sum(column_to_be_summed) as sum_column FROM Scoring_Track ORDER BY id";

I could do it this way but I have 15 columns that need SUMing separately so that's going to be one v.long query.

I thought there might be a way of stripping out the total of a column from the general query using php?

eeek

1:41 am on Apr 27, 2009 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



I could do it this way but I have 15 columns that need SUMing separately so that's going to be one v.long query.

And why is a long line of SQL a problem?

I thought there might be a way of stripping out the total of a column from the general query using php?

As was mentioned above you could just sum the columns you want in php and not even change the SQL.

Melter

1:16 pm on Apr 27, 2009 (gmt 0)

10+ Year Member



Okay wrist slapped for negative thinking. This is my crude attempt at coding the page I want which currently works but is very long-winded with all the queries.


<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 5px 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 {
margin:0;
padding:7px;
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 {
margin:0;
padding:5px;
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;
}
th.specbgtxt {
margin:0;
padding:5px;
border-left: 1px solid #C1DAD7;
border-top: 0;
background-color:#FFF;
font: bold 9px Verdana, Arial, Times, Georgia, sans-serif;
text-align:center;
color: #555;
}
</style>
</head>
<body>
<?

include '../connect.php';

mysql_pconnect(localhost,$username,$password);
@mysql_select_db($database) or die(mysql_error());

$query="SELECT * FROM Scoring_Track ORDER BY id";

$result=mysql_query($query)or die(mysql_error());

$dpquery="SELECT SUM(dylanplayerp) AS dpp FROM Scoring_Track";
$dpresult=mysql_query($dpquery) or die(mysql_error());
$dvquery="SELECT SUM(dylanvictoryp) AS dvp FROM Scoring_Track";
$dvresult=mysql_query($dvquery) or die(mysql_error());
$dtquery="SELECT SUM(dylantributep) AS dtp FROM Scoring_Track";
$dtresult=mysql_query($dtquery) or die(mysql_error());

$jpquery="SELECT SUM(jamesplayerp) AS jpp FROM Scoring_Track";
$jpresult=mysql_query($jpquery) or die(mysql_error());
$jvquery="SELECT SUM(jamesvictoryp) AS jvp FROM Scoring_Track";
$jvresult=mysql_query($jvquery) or die(mysql_error());
$jtquery="SELECT SUM(jamestributep) AS jtp FROM Scoring_Track";
$jtresult=mysql_query($jtquery) or die(mysql_error());

$mpquery="SELECT SUM(markplayerp) AS mpp FROM Scoring_Track";
$mpresult=mysql_query($mpquery) or die(mysql_error());
$mvquery="SELECT SUM(markvictoryp) AS mvp FROM Scoring_Track";
$mvresult=mysql_query($mvquery) or die(mysql_error());
$mtquery="SELECT SUM(marktributep) AS mtp FROM Scoring_Track";
$mtresult=mysql_query($mtquery) or die(mysql_error());

$shpquery="SELECT SUM(shaunplayerp) AS shpp FROM Scoring_Track";
$shpresult=mysql_query($shpquery) or die(mysql_error());
$shvquery="SELECT SUM(shaunvictoryp) AS shvp FROM Scoring_Track";
$shvresult=mysql_query($shvquery) or die(mysql_error());
$shtquery="SELECT SUM(shauntributep) AS shtp FROM Scoring_Track";
$shtresult=mysql_query($shtquery) or die(mysql_error());

$sipquery="SELECT SUM(simonplayerp) AS sipp FROM Scoring_Track";
$sipresult=mysql_query($sipquery) or die(mysql_error());
$sivquery="SELECT SUM(simonvictoryp) AS sivp FROM Scoring_Track";
$sivresult=mysql_query($sivquery) or die(mysql_error());
$sitquery="SELECT SUM(simontributep) AS sitp FROM Scoring_Track";
$sitresult=mysql_query($sitquery) or die(mysql_error());

$dpptotal=mysql_result($dpresult,"dpp");
$dvptotal=mysql_result($dvresult,"dvp");
$dtptotal=mysql_result($dtresult,"dtp");
$jpptotal=mysql_result($jpresult,"jpp");
$jvptotal=mysql_result($jvresult,"jvp");
$jtptotal=mysql_result($jtresult,"jtp");
$mpptotal=mysql_result($mpresult,"mpp");
$mvptotal=mysql_result($mvresult,"mvp");
$mtptotal=mysql_result($mtresult,"mtp");
$shpptotal=mysql_result($shpresult,"shpp");
$shvptotal=mysql_result($shvresult,"shvp");
$shtptotal=mysql_result($shtresult,"shtp");
$sipptotal=mysql_result($sipresult,"sipp");
$sivptotal=mysql_result($sivresult,"sivp");
$sitptotal=mysql_result($sitresult,"sitp");

$num=mysql_numrows($result);

mysql_close();
?>

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

<?
$i=0;
while ($i < $num)
while($row = mysql_fetch_array($result)) {
?>

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

<?
$i++;
}
echo "</table>";
?>

<table id="totaltable" cellspacing="0" summary="The Total Points">
<tr>
<th abbr="The Showdown" class="nobg">The Showdown</th>
<th colspan="2" abbr="Dylan">Dylan</th>
<th colspan="2" abbr="James">James</th>
<th colspan="2" abbr="Mark">Mark</th>
<th colspan="2" abbr="Shaun">Shaun</th>
<th colspan="2" abbr="Simon">Simon</th>
</tr>
<tr>
<th class="specalt">&nbsp;</th>
<th class="specalt"><? echo $dpptotal; ?></th>
<th class="specalt"><? echo $dvptotal+$dtptotal; ?></th>
<th class="specalt"><? echo $jpptotal; ?></th>
<th class="specalt"><? echo $jvptotal+$jtptotal; ?></th>
<th class="specalt"><? echo $mpptotal; ?></th>
<th class="specalt"><? echo $mvptotal+$mtptotal; ?></th>
<th class="specalt"><? echo $shpptotal; ?></th>
<th class="specalt"><? echo $shvptotal+$shtptotal; ?></th>
<th class="specalt"><? echo $sipptotal; ?></th>
<th class="specalt"><? echo $sivptotal+$sitptotal; ?></th>
</tr>
<tr>
<th class="specbgtxt">Total Points</th>
<th colspan="2" class="specbgtxt"><? echo $dpptotal+$dvptotal+$dtptotal; ?></th>
<th colspan="2" class="specbgtxt"><? echo $jpptotal+$jvptotal+$jtptotal; ?></th>
<th colspan="2" class="specbgtxt"><? echo $mpptotal+$mvptotal+$mtptotal; ?></th>
<th colspan="2" class="specbgtxt"><? echo $shpptotal+$shvptotal+$shtptotal; ?></th>
<th colspan="2" class="specbgtxt"><? echo $sipptotal+$sivptotal+$sitptotal; ?></th>
</tr>
</table>
<div class="clear"></div>

<?
mysql_free_result($result);
?>

</body>


I've tried the two examples given and the altered SELECT statement just gives me a timeout error. I did it this way.


$query="SELECT *,
SUM(dylanplayerp) AS dpp,
SUM(dylanvictoryp) AS dvp,
SUM(dylantributep) AS dtp,
SUM(jamesplayerp) AS jpp,
SUM(jamesvictoryp) AS jvp,
SUM(jamestributep) AS jtp,
SUM(markplayerp) AS mpp,
SUM(markvictoryp) AS mvp,
SUM(marktributep) AS mtp,
SUM(shaunplayerp) AS shpp,
SUM(shaunvictoryp) AS shvp,
SUM(shauntributep) AS shtp,
SUM(simonplayerp) AS sipp,
SUM(simonvictoryp) AS sivp,
SUM(simontributep) AS sitp
FROM Scoring_Track
GROUP BY id
ORDER BY id";

$result=mysql_query($query)or die(mysql_error());

$dpptotal=mysql_result($result,"dpp");
$dvptotal=mysql_result($result,"dvp");
$dtptotal=mysql_result($result,"dtp");
$jpptotal=mysql_result($result,"jpp");
$jvptotal=mysql_result($result,"jvp");
$jtptotal=mysql_result($result,"jtp");
$mpptotal=mysql_result($result,"mpp");
$mvptotal=mysql_result($result,"mvp");
$mtptotal=mysql_result($result,"mtp");
$shpptotal=mysql_result($result,"shpp");
$shvptotal=mysql_result($result,"shvp");
$shtptotal=mysql_result($result,"shtp");
$sipptotal=mysql_result($result,"sipp");
$sivptotal=mysql_result($result,"sivp");
$sitptotal=mysql_result($result,"sitp");


The other way using just the SELECT * statement gave me an unexpected } error. Did that one this way which is probably very wrong.


$total = 0;
foreach($row['dylanplayerp'] as $c){
$dpptotal = $total + $c}
<tr>
<th class="specalt"><? echo $dpptotal; ?></th>
</tr>

eeek

10:21 pm on Apr 27, 2009 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



foreach($row['dylanplayerp'] as $c){
$dpptotal = $total + $c}

Needs a semicolon before the }

Melter

9:07 am on Apr 28, 2009 (gmt 0)

10+ Year Member



Okay threw the stray semicolon in and I'm getting

Invalid argument supplied for foreach()
on the line in red

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

eeek

11:08 pm on Apr 28, 2009 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



That means $row['dylanplayerp'] isn't an array. What you really want to do is to fetch $row inside a loop which does the add.

Melter

10:23 am on Apr 29, 2009 (gmt 0)

10+ Year Member



Okay I'm not getting this. I already have a loop that goes through each row to display the columns in a table so I tried it that way but I'm doing something wrong just keep getting errors. I also looked up turning a column into an array which seemed logical but that doesn't work either.

Going to simplify things to make it easier for me to see what's going on. Let's say I have a table called Scores and it looks like this:

id¦ A ¦ B
1 ¦ 1 ¦ 2
2 ¦ 3 ¦ 2
3 ¦ 1 ¦ 1
4 ¦ 2 ¦ 3

Okay so the simplified code below displays a table showing those columns and the scores in them. The second table then needs to add the scores in column A and column B and display them individually so in this case totals would be 7 and 8 respectively. Then these totals are added together to show a full total which would be 15.

<?
$query="SELECT * FROM Scores ORDER BY id";
$result=mysql_query($query)or die(mysql_error());
$num=mysql_numrows($result);
mysql_close();
?>

<table>First Table Here
<?
$i=0;
while ($i < $num)
while($row = mysql_fetch_array($result)) {
?>
<tr>
<th>echo $row['A'];</th>
<th>echo $row['B'];</th>
</tr>
<?
$i++;
}
echo "</table>";
?>

<?
$total = 0;
foreach($array['A'] as $c)
{
$colAtotal = $total + $c;
}
foreach($array['B'] as $c)
{
$colBtotal = $total + $c;
}
?>


<table>Second Table With Totals
<tr>
<th>echo $colAtotal;</th>
<th>echo $colBtotal;</th>
</tr>
<tr>
<th>echo $colAtotal+$colBtotal;</th>
</tr>
</table>

<?
mysql_free_result($result);
?>

So the bit in red is the non-working area. Using this example code can some kind soul please change this to show me how it should work then I can adapt it for my full code. Thanks.

Pico_Train

7:55 pm on Apr 29, 2009 (gmt 0)

10+ Year Member



<?
$total = 0;
foreach($array['A'] as $c)
{
$colAtotal = $total + $c;
}
foreach($array['B'] as $c)
{
$colBtotal = $total + $c;
}
?>

This is outside your while loop, that might be something to look at. And $array should probably be $row.

Melter

9:25 pm on Apr 29, 2009 (gmt 0)

10+ Year Member



Yeah you're right it should be $row. I've got arrays, loops, rows, semicolons and the rest spiralling out of control in my head at the moment. I think I'd just tried to convert a column into an array without success so that's where that mindblock came from.

I've tried that statement in the loop and out of it and either way leaves me with an invalid argument error because I'm assuming it considers $row['whatever'] as not an array.

This is the point I'm stuck with at the moment.

As I can't go back and edit the dodgy post I'll repeat it here corrected.

<?
$query="SELECT * FROM Scores ORDER BY id";
$result=mysql_query($query)or die(mysql_error());
$num=mysql_numrows($result);
mysql_close();
?>

<table>First Table Here
<?
$i=0;
while ($i < $num)
while($row = mysql_fetch_array($result)) {
?>
<tr>
<th>echo $row['A'];</th>
<th>echo $row['B'];</th>
</tr>
<?
$i++;
}
echo "</table>";
?>

<?
$total = 0;
foreach($row['A'] as $c)
{
$colAtotal = $total + $c;
}
foreach($row['B'] as $d)
{
$colBtotal = $total + $d;
}
?>

<table>Second Table With Totals
<tr>
<th>echo $colAtotal;</th>
<th>echo $colBtotal;</th>
</tr>
<tr>
<th>echo $colAtotal+$colBtotal;</th>
</tr>
</table>

<?
mysql_free_result($result);
?>

midtempo

9:57 pm on Apr 29, 2009 (gmt 0)

10+ Year Member



i don't have the time to check, but at a quick guess i'd suggest try moving mysql_close to the end of the document.

Melter

12:04 pm on Apr 30, 2009 (gmt 0)

10+ Year Member



Yep moved the mysql_close but that doesn't change the foreach statement throwing up an error in the loop or out of it.

Blimey this is turning into a right saga. Can't believe adding up a few numbers can be so difficult :).

midtempo

12:17 pm on Apr 30, 2009 (gmt 0)

10+ Year Member



try this

<?
$query="SELECT * FROM Scores ORDER BY id";
$result=mysql_query($query)or die(mysql_error());
$num=mysql_numrows($result);
$colAtotal = 0;
$colBtotal = 0;
?>

<table>First Table Here
<?
for($i=0; $i<$num; $i++) {
$row = mysql_fetch_array($result);
?>
<tr>
<th><?= echo $row['A']; ?></th>
<th><?= echo $row['B']; ?></th>
</tr>
<?
$colAtotal += $row['A'];
$colBtotal += $row['B'];
}
?>
</table>

<table>Second Table With Totals
<tr>
<th><?= echo $colAtotal;?></th>
<th><?= echo $colBtotal; ?></th>
</tr>
<tr>
<th><?= echo $colAtotal+$colBtotal; ?></th>
</tr>
</table>

<? mysql_close();
mysql_free_result($result);
?>

Melter

3:20 pm on Apr 30, 2009 (gmt 0)

10+ Year Member



Genius! Gold star award for midtempo. Many thanks.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 2:41 am Apr 29, 2026