Sum of rows

Senior WebHelper Joined: 21 Apr 2003 Posts: 153

Is there an easy way to add up the values of rows in a colum.

Posted: Sat Apr 26, 2003 3:40 pm (21 years, 8 months ago)

I use this:

Code:

SELECT SUM(FieldName) from TableName

Senior WebHelper Joined: 21 Apr 2003 Posts: 153

Does this need to be queried, how do I put this into a script to display the sum.

Posted: Sat Apr 26, 2003 9:03 pm (21 years, 8 months ago)

I just use:

Code:

$total = mysql_query("SELECT SUM(some_field) from some_table");
echo $total

HTH

Senior WebHelper Joined: 21 Apr 2003 Posts: 153

It echos Resource id #2 when I use this code, any ideas why?

Code:

$total = mysql_query("SELECT SUM(ab) from Robinson_Stats_2");
echo $total;

The field as 3 rows 100, 69, 122, so it should echo 291 right?

Team Member Joined: 06 Jan 2002 Posts: 2564

You have to fetch the results first Wink

http://www.php.net/manual/en/function.mysql-fetch-array.php

Senior WebHelper Joined: 21 Apr 2003 Posts: 153

I dont get how to put this together, here is what I have and it echos Array

Code:

$total = mysql_query("SELECT SUM(ab) from Robinson_Stats_2");
$display = mysql_fetch_array($total);
echo $display;

Posted: Sat Apr 26, 2003 11:46 pm (21 years, 8 months ago)

OK. Here's what I have that does what you want:

Code:

mysql_connect (<dbhost>, <db_user>, <db_pass>);
mysql_select_DB(<db_name>);
$total = mysql_query("SELECT SUM(someField) FROM someTable");
echo "$total[0]";

ooops, looks like I forgot to put the [0] bit in last time (guess that'll teach me to check that in the advert break between progs on BBC1 Smile

[edit]
Oops, looks like I should double check what I post to make sure I'm posting the right thing Smile

[/edit]

Senior WebHelper Joined: 21 Apr 2003 Posts: 153

This is what I have and its not displaying anything.

Code:

$total = mysql_query("SELECT COUNT(ab) FROM Robinson_Stats_2");
echo "$total[0]";

I did connect to the database too.

Senior WebHelper Joined: 21 Apr 2003 Posts: 153

I really need to find this out, I am supposed to have the stats part done by tomorrow their first game. So if anyone knows how to get this to work please let me know.

WebHelper Joined: 01 Mar 2003 Posts: 69 Location: Texas

Assuming that you have already connected to the database...

Code:

$sql = "SELECT count(ab) as at_bats
FROM Robinson_Stats_2";

if ( !($result = mysql_query($sql)) )
{
die('Could not obtain at bats information');
}

$row = mysql_fetch_assoc($result) ;
$total = $row['at_bats'];

By using mysql_fetch_assoc() you get an associative array of column results. You should name any column results that are calculated, like your count(), so that you have a descriptive name to work with. Then you reference the results using the array, indexed by column name.

Dave

Senior WebHelper Joined: 21 Apr 2003 Posts: 153

This code tells me how many rows there are, im trying to add the content of the rows up.

Code:

$sql = "SELECT count(ab) as at_bats
FROM Robinson_Stats_2";
if ( !($result = mysql_query($sql)) )
{
die('Could not obtain at bats information');
}
$row = mysql_fetch_assoc($result) ;
$total = $row['at_bats'];
echo "$total"

Team Member Joined: 06 Jan 2002 Posts: 2564

As someone said above, replace:

Code:

count(ab)

with

Code:

sum(ab)

WebHelper Joined: 01 Mar 2003 Posts: 69 Location: Texas

Daniel wrote:

As someone said above, replace:

Code:

count(ab)

with

Code:

sum(ab)

I was going with the code that was just a couple of posts above mine. If each "at bat" was stored as a row, then you would count them. If the ab column contains a number, then you sum them. It wasn't clear from your description which way you needed to go.

But I'm assuming that the code is working since you said you are getting data back now. Cool

At this point you have a template of sorts to get you going. Good luck!

Dave