Novice trying to SUM array elements from query results.

I thought that this was going to be super easy (and it probably is),
but I couldn't figure it out.
I have results from a MySQL query which include PartNumber, Qty, Cost,
and Price.
In one part of the report, I want to only display Sum/Total info.

As an example, let's say there are 5 rows of data, and I want to know
only the sum total of $result["qty"] from all 5 rows.

I was expecting to use array_sum(), but have come to discover that
this only sums all the elements of a single array.

Any pointers?
~Mo

..oO(Mo)

>I thought that this was going to be super easy (and it probably is),
>but I couldn't figure it out.
>I have results from a MySQL query which include PartNumber, Qty, Cost,
>and Price.
>In one part of the report, I want to only display Sum/Total info.
>
>As an example, let's say there are 5 rows of data, and I want to know
>only the sum total of $result["qty"] from all 5 rows.
>
>I was expecting to use array_sum(), but have come to discover that
>this only sums all the elements of a single array.
>
>Any pointers?

I would let the database do it. Who cares about a simple second query to
get the totals? I doubt that this would cause any performance problem.

Micha

On Jun 13, 12:27 pm, Michael Fesser <neti...@gmx.de> wrote:
> .oO(Mo)
>
> >I thought that this was going to be super easy (and it probably is),
> >but I couldn't figure it out.
> >I have results from a MySQL query which include PartNumber, Qty, Cost,
> >and Price.
> >In one part of the report, I want to only display Sum/Total info.
>
> >As an example, let's say there are 5 rows of data, and I want to know
> >only the sum total of $result["qty"] from all 5 rows.
>
> >I was expecting to use array_sum(), but have come to discover that
> >this only sums all the elements of a single array.
>
> >Any pointers?
>
> I would let the database do it. Who cares about a simple second query to
> get the totals? I doubt that this would cause any performance problem.
>
> Micha

There are two reasons why I want to do it in my report coding:
1) Some of these values are currently stored in the DB, but
unfortunately, they cannot be depended upon to be accurate or up to
date.
Until we can rebuild the code and eliminate those bugs, I'd like to be
able to get reliable info.
2) It would be a good learning experience.

You mentioned a second query.
Is that your suggestion?
Just run a whole new query instead of extracting the info from the
existing dataset?
Is there a more efficient way to do this?

~Mo

Mo wrote:
> On Jun 13, 12:27 pm, Michael Fesser <neti...@gmx.de> wrote:
>> .oO(Mo)
>>
>> >I thought that this was going to be super easy (and it probably is),
>> >but I couldn't figure it out.
>> >I have results from a MySQL query which include PartNumber, Qty,
>> >Cost, and Price.
>> >In one part of the report, I want to only display Sum/Total info.
>>
>> >As an example, let's say there are 5 rows of data, and I want to
>> >know only the sum total of $result["qty"] from all 5 rows.
>>
>> >I was expecting to use array_sum(), but have come to discover that
>> >this only sums all the elements of a single array.
>>
>> >Any pointers?
>>
>> I would let the database do it. Who cares about a simple second
>> query to get the totals? I doubt that this would cause any
>> performance problem.
>>
>> Micha
>
> There are two reasons why I want to do it in my report coding:
> 1) Some of these values are currently stored in the DB, but
> unfortunately, they cannot be depended upon to be accurate or up to
> date.
If you are getting the data from the db and you cannot rely on them, then
why does summing them outside of the SQL make them any more accurate?

..oO(Mo)

>On Jun 13, 12:27 pm, Michael Fesser <neti...@gmx.de> wrote:
>> .oO(Mo)
>>
>> >I thought that this was going to be super easy (and it probably is),
>> >but I couldn't figure it out.
>> >I have results from a MySQL query which include PartNumber, Qty, Cost,
>> >and Price.
>> >In one part of the report, I want to only display Sum/Total info.
>>
>> >As an example, let's say there are 5 rows of data, and I want to know
>> >only the sum total of $result["qty"] from all 5 rows.
>>
>> >I was expecting to use array_sum(), but have come to discover that
>> >this only sums all the elements of a single array.
>>
>> >Any pointers?
>>
>> I would let the database do it. Who cares about a simple second query to
>> get the totals? I doubt that this would cause any performance problem.
>
>There are two reasons why I want to do it in my report coding:
>1) Some of these values are currently stored in the DB, but
>unfortunately, they cannot be depended upon to be accurate or up to
>date.

OK, but where does the report data come from? How can this data be more
accurate then the data in the DB?

>Until we can rebuild the code and eliminate those bugs, I'd like to be
>able to get reliable info.
>2) It would be a good learning experience.

OK. This would probably mean to loop through the array with foreach and
sum up the values.

>You mentioned a second query.
>Is that your suggestion?

In this case - yes, most likely.

>Just run a whole new query instead of extracting the info from the
>existing dataset?

A new query isn't that expensive if the connection to the DB server is
already established. Looping through an array and doing the math on your
own also takes some time, while the DB can access all the wanted records
almost immediately and do the math with highly optimized code.

It might even be possible to do it all in one query with some clever
GROUP BY or UNION clauses.

>Is there a more efficient way to do this?

I don't think you'll run into efficieny problems here. Personally I just
use what seems to be easiest and most simple solution. And if a second
query gets me the results I want quite instantly, then I just do it.

Optimization comes later. It requires to profile the code in order to
find the real bottlenecks. Replacing a query with handwritten array
looping can be considered "premature optimization", because usually the
really time-consuming parts of the code are somewhere else.

Micha

On Jun 13, 2:40 pm, Michael Fesser <neti...@gmx.de> wrote:
> .oO(Mo)
>
>
>
> >On Jun 13, 12:27 pm, Michael Fesser <neti...@gmx.de> wrote:
> >> .oO(Mo)
>
> >> >I thought that this was going to be super easy (and it probably is),
> >> >but I couldn't figure it out.
> >> >I have results from a MySQL query which include PartNumber, Qty, Cost,
> >> >and Price.
> >> >In one part of the report, I want to only display Sum/Total info.
>
> >> >As an example, let's say there are 5 rows of data, and I want to know
> >> >only the sum total of $result["qty"] from all 5 rows.
>
> >> >I was expecting to use array_sum(), but have come to discover that
> >> >this only sums all the elements of a single array.
>
> >> >Any pointers?
>
> >> I would let the database do it. Who cares about a simple second query to
> >> get the totals? I doubt that this would cause any performance problem.
>
> >There are two reasons why I want to do it in my report coding:
> >1) Some of these values are currently stored in the DB, but
> >unfortunately, they cannot be depended upon to be accurate or up to
> >date.
>
> OK, but where does the report data come from? How can this data be more
> accurate then the data in the DB?
>
> >Until we can rebuild the code and eliminate those bugs, I'd like to be
> >able to get reliable info.
> >2) It would be a good learning experience.
>
> OK. This would probably mean to loop through the array with foreach and
> sum up the values.
>
> >You mentioned a second query.
> >Is that your suggestion?
>
> In this case - yes, most likely.
>
> >Just run a whole new query instead of extracting the info from the
> >existing dataset?
>
> A new query isn't that expensive if the connection to the DB server is
> already established. Looping through an array and doing the math on your
> own also takes some time, while the DB can access all the wanted records
> almost immediately and do the math with highly optimized code.
>
> It might even be possible to do it all in one query with some clever
> GROUP BY or UNION clauses.
>
> >Is there a more efficient way to do this?
>
> I don't think you'll run into efficieny problems here. Personally I just
> use what seems to be easiest and most simple solution. And if a second
> query gets me the results I want quite instantly, then I just do it.
>
> Optimization comes later. It requires to profile the code in order to
> find the real bottlenecks. Replacing a query with handwritten array
> looping can be considered "premature optimization", because usually the
> really time-consuming parts of the code are somewhere else.
>
> Micha

To answer everyones question about reliable query info from an
unreliable DB, the detail info in the DB is reliable, but the "total"
values are not.
It looks like storing the totals was an afterthought, and not all the
PHP pages pertaining to those fields were addressed.
Even of those which were, some look to have buggy code (funky rounding
and innacurate negative values for credit invoices).

I figure if I can pull reliable info via the reports, that would get
me going sooner, and eliminate the chances of me just recreating the
same data problems.
We could fix the data handling when we are actually on that project.

Thanks for all the insight.
I'll surely post if I have any more questions.
~Mo

Mo wrote:
> On Jun 13, 2:40 pm, Michael Fesser <neti...@gmx.de> wrote:
>> .oO(Mo)
>>
>>
>>
>> >On Jun 13, 12:27 pm, Michael Fesser <neti...@gmx.de> wrote:
>> >> .oO(Mo)
>>
>> >> >I thought that this was going to be super easy (and it probably
>> >> >is), but I couldn't figure it out.
>> >> >I have results from a MySQL query which include PartNumber, Qty,
>> >> >Cost, and Price.
>> >> >In one part of the report, I want to only display Sum/Total info.
>>
>> >> >As an example, let's say there are 5 rows of data, and I want to
>> >> >know only the sum total of $result["qty"] from all 5 rows.
>>
>> >> >I was expecting to use array_sum(), but have come to discover
>> >> >that this only sums all the elements of a single array.
>>
>> >> >Any pointers?
>>
>> >> I would let the database do it. Who cares about a simple second
>> >> query to get the totals? I doubt that this would cause any
>> >> performance problem.
>>
>> >There are two reasons why I want to do it in my report coding:
>> >1) Some of these values are currently stored in the DB, but
>> >unfortunately, they cannot be depended upon to be accurate or up to
>> >date.
>>
>> OK, but where does the report data come from? How can this data be
>> more accurate then the data in the DB?
>>
>> >Until we can rebuild the code and eliminate those bugs, I'd like to
>> >be able to get reliable info.
>> >2) It would be a good learning experience.
>>
>> OK. This would probably mean to loop through the array with foreach
>> and sum up the values.
>>
>> >You mentioned a second query.
>> >Is that your suggestion?
>>
>> In this case - yes, most likely.
>>
>> >Just run a whole new query instead of extracting the info from the
>> >existing dataset?
>>
>> A new query isn't that expensive if the connection to the DB server
>> is already established. Looping through an array and doing the math
>> on your own also takes some time, while the DB can access all the
>> wanted records almost immediately and do the math with highly
>> optimized code.
>>
>> It might even be possible to do it all in one query with some clever
>> GROUP BY or UNION clauses.
>>
>> >Is there a more efficient way to do this?
>>
>> I don't think you'll run into efficieny problems here. Personally I
>> just use what seems to be easiest and most simple solution. And if a
>> second query gets me the results I want quite instantly, then I just
>> do it.
>>
>> Optimization comes later. It requires to profile the code in order to
>> find the real bottlenecks. Replacing a query with handwritten array
>> looping can be considered "premature optimization", because usually
>> the really time-consuming parts of the code are somewhere else.
>>
>> Micha
>
> To answer everyones question about reliable query info from an
> unreliable DB, the detail info in the DB is reliable, but the "total"
> values are not.
DUH, you shouldn't be storing totals. These get calculated in the query when
you extract them. This is what everyone has been telling you.

On Jun 13, 3:46 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Mo wrote:
> > On Jun 13, 2:40 pm, Michael Fesser <neti...@gmx.de> wrote:
> >> .oO(Mo)
>
> >> >On Jun 13, 12:27 pm, Michael Fesser <neti...@gmx.de> wrote:
> >> >> .oO(Mo)
>
> >> >> >I thought that this was going to be super easy (and it probably
> >> >> >is), but I couldn't figure it out.
> >> >> >I have results from a MySQL query which include PartNumber, Qty,
> >> >> >Cost, and Price.
> >> >> >In one part of the report, I want to only display Sum/Total info.
>
> >> >> >As an example, let's say there are 5 rows of data, and I want to
> >> >> >know only the sum total of $result["qty"] from all 5 rows.
>
> >> >> >I was expecting to use array_sum(), but have come to discover
> >> >> >that this only sums all the elements of a single array.
>
> >> >> >Any pointers?
>
> >> >> I would let the database do it. Who cares about a simple second
> >> >> query to get the totals? I doubt that this would cause any
> >> >> performance problem.
>
> >> >There are two reasons why I want to do it in my report coding:
> >> >1) Some of these values are currently stored in the DB, but
> >> >unfortunately, they cannot be depended upon to be accurate or up to
> >> >date.
>
> >> OK, but where does the report data come from? How can this data be
> >> more accurate then the data in the DB?
>
> >> >Until we can rebuild the code and eliminate those bugs, I'd like to
> >> >be able to get reliable info.
> >> >2) It would be a good learning experience.
>
> >> OK. This would probably mean to loop through the array with foreach
> >> and sum up the values.
>
> >> >You mentioned a second query.
> >> >Is that your suggestion?
>
> >> In this case - yes, most likely.
>
> >> >Just run a whole new query instead of extracting the info from the
> >> >existing dataset?
>
> >> A new query isn't that expensive if the connection to the DB server
> >> is already established. Looping through an array and doing the math
> >> on your own also takes some time, while the DB can access all the
> >> wanted records almost immediately and do the math with highly
> >> optimized code.
>
> >> It might even be possible to do it all in one query with some clever
> >> GROUP BY or UNION clauses.
>
> >> >Is there a more efficient way to do this?
>
> >> I don't think you'll run into efficieny problems here. Personally I
> >> just use what seems to be easiest and most simple solution. And if a
> >> second query gets me the results I want quite instantly, then I just
> >> do it.
>
> >> Optimization comes later. It requires to profile the code in order to
> >> find the real bottlenecks. Replacing a query with handwritten array
> >> looping can be considered "premature optimization", because usually
> >> the really time-consuming parts of the code are somewhere else.
>
> >> Micha
>
> > To answer everyones question about reliable query info from an
> > unreliable DB, the detail info in the DB is reliable, but the "total"
> > values are not.
>
> DUH, you shouldn't be storing totals. These get calculated in the query when
> you extract them. This is what everyone has been telling you.

Heh, heh.
I'm not tmart, but I'm tronng :-).

I'll go look at it from that angle.

~Mo

Mo wrote:
> On Jun 13, 3:46 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> Mo wrote:
>>> On Jun 13, 2:40 pm, Michael Fesser <neti...@gmx.de> wrote:
>>>> .oO(Mo)
>>>>> On Jun 13, 12:27 pm, Michael Fesser <neti...@gmx.de> wrote:
>>>>>> .oO(Mo)
>>>>>>> I thought that this was going to be super easy (and it probably
>>>>>>> is), but I couldn't figure it out.
>>>>>>> I have results from a MySQL query which include PartNumber, Qty,
>>>>>>> Cost, and Price.
>>>>>>> In one part of the report, I want to only display Sum/Total info.
>>>>>>> As an example, let's say there are 5 rows of data, and I want to
>>>>>>> know only the sum total of $result["qty"] from all 5 rows.
>>>>>>> I was expecting to use array_sum(), but have come to discover
>>>>>>> that this only sums all the elements of a single array.
>>>>>>> Any pointers?
>>>>>> I would let the database do it. Who cares about a simple second
>>>>>> query to get the totals? I doubt that this would cause any
>>>>>> performance problem.
>>>>> There are two reasons why I want to do it in my report coding:
>>>>> 1) Some of these values are currently stored in the DB, but
>>>>> unfortunately, they cannot be depended upon to be accurate or up to
>>>>> date.
>>>> OK, but where does the report data come from? How can this data be
>>>> more accurate then the data in the DB?
>>>>> Until we can rebuild the code and eliminate those bugs, I'd like to
>>>>> be able to get reliable info.
>>>>> 2) It would be a good learning experience.
>>>> OK. This would probably mean to loop through the array with foreach
>>>> and sum up the values.
>>>>> You mentioned a second query.
>>>>> Is that your suggestion?
>>>> In this case - yes, most likely.
>>>>> Just run a whole new query instead of extracting the info from the
>>>>> existing dataset?
>>>> A new query isn't that expensive if the connection to the DB server
>>>> is already established. Looping through an array and doing the math
>>>> on your own also takes some time, while the DB can access all the
>>>> wanted records almost immediately and do the math with highly
>>>> optimized code.
>>>> It might even be possible to do it all in one query with some clever
>>>> GROUP BY or UNION clauses.
>>>>> Is there a more efficient way to do this?
>>>> I don't think you'll run into efficieny problems here. Personally I
>>>> just use what seems to be easiest and most simple solution. And if a
>>>> second query gets me the results I want quite instantly, then I just
>>>> do it.
>>>> Optimization comes later. It requires to profile the code in order to
>>>> find the real bottlenecks. Replacing a query with handwritten array
>>>> looping can be considered "premature optimization", because usually
>>>> the really time-consuming parts of the code are somewhere else.
>>>> Micha
>>> To answer everyones question about reliable query info from an
>>> unreliable DB, the detail info in the DB is reliable, but the "total"
>>> values are not.
>> DUH, you shouldn't be storing totals. These get calculated in the query when
>> you extract them. This is what everyone has been telling you.
>
> Heh, heh.
> I'm not tmart, but I'm tronng :-).
>
> I'll go look at it from that angle.
>
> ~Mo
>

Yea, for more help, try a newsgroup for your database. They can help
you a lot on proper database design as well as how to format your query.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================