[PHP] Sum a column of values from a MySQL query

[Ben C.]

I am trying to sum a query of values from a MySQL table. The code I am
using is:

---BEGIN CODE #1--------------
$sql_2 = "SELECT SUM(partpaidamount) as partpaid
FROM $tb_name
WHERE invoiceid = \"$invoiceid\"
";

$result_2 = @mysql_query($sql_2,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result_2)) {
$invoicepartpaid = $row['partpaid'];
}
---END CODE #2----------------

1) The code returns the sum of a partially paid invoice. The individual
invoice is 'partpaid'. WORKS...NO PROBLEM
2) The while() then return a list of partially paid invoices which is
$invoicepartpaid. WORKS..NO PROBLEM
3) I then want to add the list of partially paid invoices ($invoicepartpaid)
together. I AM STUCK HERE ADDING THE INVOICES AMOUNTS TOGETHER.

If anyone can help I would greatly appreciate it.

[Giz]

I think you're a big confused here. Your query will only return one row,
because you're using sum(). $invoicepartpaid will be your total for the
invoiceid specified.

-----Original Message-----
From: Ben C. [mailto:benc@cox.net]
Sent: Monday, August 04, 2003 11:42 PM
To: [email]php-general@lists.php.net[/email]
Subject: [PHP] Sum a column of values from a MySQL query

I am trying to sum a query of values from a MySQL table. The code I am
using is:

---BEGIN CODE #1--------------
$sql_2 = "SELECT SUM(partpaidamount) as partpaid
FROM $tb_name
WHERE invoiceid = \"$invoiceid\"
";

$result_2 = @mysql_query($sql_2,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result_2)) {
$invoicepartpaid = $row['partpaid'];
}
---END CODE #2----------------

[Ben C.]

Yes, I know. However, the while() loop should generate all the invoice in a
list.

-----Original Message-----
From: Giz [mailto:gizmo@gizmola.com]
Sent: Tuesday, August 05, 2003 12:03 AM
To: 'Ben C.'; [email]php-general@lists.php.net[/email]
Subject: RE: [PHP] Sum a column of values from a MySQL query


I think you're a big confused here. Your query will only return one row,
because you're using sum(). $invoicepartpaid will be your total for the
invoiceid specified.

-----Original Message-----
From: Ben C. [mailto:benc@cox.net]
Sent: Monday, August 04, 2003 11:42 PM
To: php-general@lists.php.net
Subject: [PHP] Sum a column of values from a MySQL query

I am trying to sum a query of values from a MySQL table. The code I am
using is:

---BEGIN CODE #1--------------
$sql_2 = "SELECT SUM(partpaidamount) as partpaid
FROM $tb_name
WHERE invoiceid = \"$invoiceid\"
";

$result_2 = @mysql_query($sql_2,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result_2)) {
$invoicepartpaid = $row['partpaid'];
}
---END CODE #2----------------





--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Jason Wong]

On Tuesday 05 August 2003 15:43, Ben C. wrote:

> Yes, I know. However, the while() loop should generate all the invoice in
> a list.

As has been pointed out the query only results in a single row. So how would
the while-loop "generate all the invoice[s]"? It's only ever given a single
row to play with.

And has also been pointed out, could you state clearly:

- what exactly you're trying to do
- what your database schema is
- what you tried that didn't work, and *how* it didn't work

--
Jason Wong -> Gremlins Associates -> [url]www.gremlins.biz[/url]
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
------------------------------------------
Search the list archives before you post
[url]http://marc.theaimsgroup.com/?l=php-general[/url]
------------------------------------------
/*
I appoint you ambassador to Fantasy Island!!!
*/

[Jay Blanchard]

[snip]
However, why not just SUM all the rows in the table in the query if you
just
want a total?

$sql_2 = "SELECT SUM(partpaidamount) as partpaid FROM $tb_name";
[/snip]

That is what he is doing. The SELECT SUM will only return ONE row! I am
not sure that the problem is being explained clearly though.

[Jay Blanchard]

[snip]
1) The code returns the sum of a partially paid invoice. The individual
invoice is 'partpaid'. WORKS...NO PROBLEM
2) The while() then return a list of partially paid invoices which is
$invoicepartpaid. WORKS..NO PROBLEM
3) I then want to add the list of partially paid invoices
($invoicepartpaid)
together. I AM STUCK HERE ADDING THE INVOICES AMOUNTS TOGETHER.
[/snip]

Ben...do you want to add together invoices where each ID is different?
Is that right? If so I have the answer! You need to loop through the
ID's which you are not doing....I have taken your code and added to
it....(and made changes for clarity in the e-amil)

//get invoice id's
$sql_1 = "SELECT DISTINCT(invoiceid) as inid FROM $tb_name ";
$result_1 = mysql_query($sql_1, $connection) or die(mysql_error());

while($idinvoice = mysql_fetch_array($result_1)){
// get partial paid for THIS invoice
$sql_2 = "SELECT SUM(partpaidamount) as partpaid ";
$sql_2 .= "FROM $tb_name ";
$sql_2 .= "WHERE invoiceid = '" . $idinvoice['inid'] . "' ";
$result_2 = @mysql_query($sql_2,$connection) or die(mysql_error());
//you will only be returning ONE row, so no need for while loop
$row = mysql_fetch_array($result_2);
$invoicepartpaid = $row['partpaid'];
$total = $total + $invoicepartpaid;
} // loop to NEXT invoice

print(number_format($total, 2, '', ','). "\n");

Does this make sense? You must loop through each ID and query for that
ID's partial amount.

[Cpt John W. Holmes]

Sorry, Jay, but that's a horrible method. You could just run one query with
a GROUP BY clause to get the sum for each invoiceID, instead of running
multiple queries like you've mentioned...

$query = "SELECT invoiceid, SUM(partpaidamount) AS partpaid FROM $tb_name
GROUP BY invoiceid";
$rs = mysql_query($query) or die(mysql_error());
while($r = mysql_fetch_assoc($rs))
{ echo "Invoice: {$r['invoiceid']}, SUM: {$r['partpaid']}"; }

---John Holmes...

PS: Sorry for the top post, but I'm at work and outlook express sucks. :)

----- Original Message -----
From: "Jay Blanchard" <jay.blanchard@niicommunications.com>
To: "Ben C." <benc@cox.net>; <php-general@lists.php.net>
Sent: Tuesday, August 05, 2003 8:29 AM
Subject: RE: [PHP] Sum a column of values from a MySQL query


[snip]
1) The code returns the sum of a partially paid invoice. The individual
invoice is 'partpaid'. WORKS...NO PROBLEM
2) The while() then return a list of partially paid invoices which is
$invoicepartpaid. WORKS..NO PROBLEM
3) I then want to add the list of partially paid invoices
($invoicepartpaid)
together. I AM STUCK HERE ADDING THE INVOICES AMOUNTS TOGETHER.
[/snip]

Ben...do you want to add together invoices where each ID is different?
Is that right? If so I have the answer! You need to loop through the
ID's which you are not doing....I have taken your code and added to
it....(and made changes for clarity in the e-amil)

//get invoice id's
$sql_1 = "SELECT DISTINCT(invoiceid) as inid FROM $tb_name ";
$result_1 = mysql_query($sql_1, $connection) or die(mysql_error());

while($idinvoice = mysql_fetch_array($result_1)){
// get partial paid for THIS invoice
$sql_2 = "SELECT SUM(partpaidamount) as partpaid ";
$sql_2 .= "FROM $tb_name ";
$sql_2 .= "WHERE invoiceid = '" . $idinvoice['inid'] . "' ";
$result_2 = @mysql_query($sql_2,$connection) or die(mysql_error());
//you will only be returning ONE row, so no need for while loop
$row = mysql_fetch_array($result_2);
$invoicepartpaid = $row['partpaid'];
$total = $total + $invoicepartpaid;
} // loop to NEXT invoice

print(number_format($total, 2, '', ','). "\n");

Does this make sense? You must loop through each ID and query for that
ID's partial amount.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Jay Blanchard]

[snip]
Sorry, Jay, but that's a horrible method. You could just run one query
with
a GROUP BY clause to get the sum for each invoiceID, instead of running
multiple queries like you've mentioned...

$query = "SELECT invoiceid, SUM(partpaidamount) AS partpaid FROM
$tb_name
GROUP BY invoiceid";
$rs = mysql_query($query) or die(mysql_error());
while($r = mysql_fetch_assoc($rs))
{ echo "Invoice: {$r['invoiceid']}, SUM: {$r['partpaid']}"; }

---John Holmes...

PS: Sorry for the top post, but I'm at work and outlook express sucks.
:)
[/snip]

No problem bro'. I knew it was the long way around I was trying to
understand if that was what Ben wanted. And I am so old school that
almost always avoid ternary! :) And I feel for you....Express...blech!

[Miles Thompson]

From bitter experience I'll suggest that it's a REALLY GOOD idea to store
invoice totals, subtotals, etc. in either the invoice header record. If not
rounding errors, or some small change in logic, can come back and bite you
- accountants get *really* upset when that 3000.00 invoice becomes 3000.01
or 2999.99.

Know what else? THEY'LL NEVER LET YOU FORGET.

Don't call it denormalization - call it history.

Cheers - Miles


At 03:53 PM 8/5/2003 +0800, Jason Wong wrote:

>On Tuesday 05 August 2003 15:43, Ben C. wrote:

> > Yes, I know. However, the while() loop should generate all the invoice in
> > a list.

>
>As has been pointed out the query only results in a single row. So how would
>the while-loop "generate all the invoice[s]"? It's only ever given a single
>row to play with.
>
>And has also been pointed out, could you state clearly:
>
>- what exactly you're trying to do
>- what your database schema is
>- what you tried that didn't work, and *how* it didn't work
>
>--
>Jason Wong -> Gremlins Associates -> [url]www.gremlins.biz[/url]
>Open Source Software Systems Integrators
>* Web Design & Hosting * Internet & Intranet Applications Development *
>------------------------------------------
>Search the list archives before you post
>[url]http://marc.theaimsgroup.com/?l=php-general[/url]
>------------------------------------------
>/*
>I appoint you ambassador to Fantasy Island!!!
>*/
>
>
>--
>PHP General Mailing List ([url]http://www.php.net/[/url])
>To unsubscribe, visit: [url]http://www.php.net/unsub.php[/url]