[RESOLVED] Displaying Total Value of Database Table

obrienkev

Hi all,

I want to display the total value for a count column.

How do I do this?

Here's my code...

$query = "SELECT countries, sum(count) FROM statistics GROUP BY countries";
$result = mysql_query($query) or die(mysql_error());
$num_results = mysql_num_rows($result);

while ($row=mysql_num_rows($result))
{
echo '<p>Total Number of Participants: '.$row['sum'].'</p>';
}

why won't this work?

thanks.

thorpe

because sum is a function not a field.

try

SELECT countries, sum(count) AS sumofcount FROM statistics GROUP BY countries

then use $row['sumofcount']; to grab it.

ps: count is also a function so you might have some issues naming a field count.

superwormy

'count' is also often a reserved word, you shouldn't use it for field names.

obrienkev

OK. Still stuck on this. Here's my code now...


$query = "SELECT countries, sum(count) AS totalCount ". 

 "FROM statistics ".

 "GROUP BY countries";

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

$num_results = mysql_num_rows($result);

$count = 0;

for($i=0; $i<=$num_results; $i++)

{

$count += $row['totalCount'];

}

echo '<p>Total Number of Participants: '.$count.'</p>';

It displays 0 when it should display 4

I want to get the total i.e. add each value in each row together.

How do I do this?

tekky

since you didnt listen about COUNT being reserved....

//note count is surrounded by ticks (`) to ensure its not read as a function
$query = 'SELECT sum(`count`) AS totalCount FROM statistics GROUP BY countries';

$result = mysql_query($query) or die(mysql_error());
$count = $row['totalCount'];
echo '<p>Total Number of Participants: '.$count.'</p>';

obrienkev

didn't work. Doesn't display $count value. Changed count to counter...

$query = "SELECT sum(counter) AS totalCount ". 
			 	 "FROM statistics ".
				 "GROUP BY countries";
$result = mysql_query($query) or die(mysql_error());

$count = $row['totalCount'];

echo '<p>Total Number of Participants: '.$count.'</p>';

mrhappiness

you forget fetching the result

mysql_fetch_assoc and all those beautiful functions... 🙂

obrienkev

I have been playing around with them for the past while. No difference though.

Tried...

$row = mysql_fetch_array($result);
$count = $row['totalCount'];

mrhappiness

show the complete code and the content of $row please

obrienkev

Code...

include('../db_connect.php');

$query = "SELECT sum(counter) AS totalCount ". 
			 	 "FROM statistics ".
				 "GROUP BY countries";
$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_assoc($result);
$count = $row['totalCount'];

echo '<p>Total Number of Participants: '.$count.'</p>';

Displays... Total Number of Participants: 0

mrhappiness

mrhappiness wrote:
and the content of $row please

i don't see it

hint: [man]print_r[/man]

btw: why do you use group by if you only select the sum?

either select the sum per country and the corresponding country (in this case you need GROUP BY) or select the sum (overall countries)

obrienkev

Have it guys! I removed the GROUP BY clause...

$query = "SELECT sum(counter) AS totalCount ".
                 "FROM statistics";

Thanks for all your help!

But why did the GROUP BY affect it like it did?

mrhappiness

maybe there's a country with a counter value of 0?