Problem with SUM with SQL

[kumschick]

Hi, I have this values in the MySQL query.
Who you I cound them all thogether?

 ID ACCOUNT
  7 6368 
  6 7866 
  5 10000 
  4 352 
  3 2549 
  2 63 
  1 101 

the resulat sould be: 27299 = ( 6368 + 7866 + ... + 101)

how could I cunt everything together in mysql and put this output to a $var in PHP. Has to work with MySQL 4.0 or 4.1

[rincewind456]

SELECT SUM(account) from YOUR_TABLE_NAME

[kumschick]

Why doesn't this work? I dont get any output! (blank page and no errors) !!!!

<?php

$host     = "SERVERNAME";
$username = "USERNAME";
$password = "PASSWORD";
$database = "DATABASE";

mysql_connect($host,$username,$password) or die( "Unable to connect to database");  

@mysql_select_db($database) or die( "Unable to select database");  

$sql = "SELECT SUM(total) FROM account";  

$result = mysql_query($sql);  

$row = mysql_fetch_assoc($result);  

echo "$row[post_total]";

?>

[rincewind456]

If the column names from you first post are id and account, why are you trying to total up a column called total 🙂

if the names you've given are correct then your query string should be

SELECT SUM(account) from account.

[kumschick]

i made a mistake in the post, put the code is correct!

TableName = stats
ColName = accout

sorry!

But it don't work! and if I test in in Command Prompt with:

"SELECT SUM(account) FROM stats" -> it works

What is wrong?

[rincewind456]

This row is not calling your result

echo "$row[post_total]";

as you are only calling 1 result it would be quicker to use mysql_fetch_row than mysql_fetch_assoc.

If you use mysql_fetch_row then you would call the answer by doing this

print $row[0];

If you want to use mysql_fetch_assoc then your answer can be got by using this

print $row['SUM(account)'];

[jmartinez]

I am having the issue of not having the value show also.

Here is what the code looks like

<?
include("../../global/dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$sql = 'SELECT SUM(total) FROM invoices WHERE status LIKE "Past Due"';
$result = mysql_query($sql);  

$row = mysql_fetch_row($result); 
print $row[0];
?>

any ideas why I can not see the value?

[rincewind456]

whats your DB table structure?

[jmartinez]

Table Name : invoices

Field | Type

id | int(10)
date | varchar(10)
billto | varchar(50)
address | varchar(50)
city | varchar(50)
state | char(2)
zip | int(5)
description | text
subtotal | int(10)
down | int(10)
balance | int(10)
additional | text
total | int(10)
st1 | int(10)
status | varchar(25)
jstreet | varchar(50)
jcity | varchar(50)
jstate | varchar(2)
jzip | varchar(5)

[rincewind456]

Try changing your query to

$sql = "SELECT SUM(total) FROM invoices WHERE status = 'Past Due'"; 

[jmartinez]

This is odd everything I read says that I should be getting an output. Here is the full block of code so you can see. All other query's on the page are working fine.

<table width="700" cellpadding="1" cellspacing="1" border="0">
<tr>
<td width="300"><div class="bheader">Total Past Due</div></td>
<td width="100"></td>
<td width="300"><div class="echotextleft>

<?
include("../../global/dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$sql = "SELECT SUM(total) FROM invoices WHERE status = 'Past Due'";
$result = mysql_query($sql);  

$row = mysql_fetch_row($result); 
print $row[0];
?>

<div>
</td>
</tr>
</table>

You can see that I have made the change as you suggested.

[rincewind456]

Are you certain that there is a result to return?
That query works just fine, as long as the status field is exactly equal to "Past Due".
If you have something like phpmyadmin or access through the command line run the query there to see what result you get.
But my guess is that the DB field status isn't saying Past Due and therefore can't return a result.

[jmartinez]

Ok, yes there were marked correctly. I tested the code snip all by itself and it worked just fine. Made me wonder about the rest of the code. The only thing that I could think of is the <div> tag creating an issue. Removed it and works great. Replaced it and it works great. Not sure what the problem was with it, but it does indeed work. Thank you for your input. I appriciate the help! :o

P.S. I consider this resolved, however I piggy backed on someone else's topic. I noticed that this person has not marked as resolved or even commented on for a while. Is there a way I can mark the thread as resolved?