Discussion:
Sum computed fields for groups
(too old to reply)
unknown
2009-02-21 01:06:12 UTC
Permalink
Here is my situation: I have a report that I have separated
into 2 groups of data. I am taking group 2 data and using a
computed field to give me a sum() of the material costs for
that group of data. I then want to sum() all of those
computed fields for a "group 1" total. When I write the
equation to sum(compute_1 for group 1) the value that is
returned is exactly double what it should be. I have tried
this several times with different equations and no luck. I
read online from a Powerbuilder case in 2003, that it is not
possible to get an aggregate sum from an aggregate unless
you add some specific script to the report, which I do not
know how, nor want to do. Has there been any changes with
Infomaker since then that allow me to sum up a summed value
in a group? Thanks, Dan
oceanandmtns
2009-02-21 21:42:44 UTC
Permalink
Try this:

sum(compute_1 for group 1) DISTINCT - I can't remember if the
Distinct should be on the outside of the sum or inside but you can try
both to see which one works.

Or you could create an embedded with the same calculations and place
it inside the report and it may work.

Or you may not have a correct join on the table so it's giving data to
you twice.
Post by unknown
Here is my situation: I have a report that I have separated
into 2 groups of data.  I am taking group 2 data and using a
computed field to give me a sum() of the material costs for
that group of data.  I then want to sum() all of those
computed fields for a "group 1" total.  When I write the
equation to sum(compute_1 for group 1) the value that is
returned is exactly double what it should be.  I have tried
this several times with different equations and no luck.  I
read online from a Powerbuilder case in 2003, that it is not
possible to get an aggregate sum from an aggregate unless
you add some specific script to the report, which I do not
know how, nor want to do.  Has there been any changes with
Infomaker since then that allow me to sum up a summed value
in a group?  Thanks, Dan
unknown
2009-02-27 17:13:23 UTC
Permalink
Utilizing the DISTINCT within the () seemed to work, I will
do some more testing, but so far, so good. Thanks.
Post by oceanandmtns
sum(compute_1 for group 1) DISTINCT - I can't remember if
the Distinct should be on the outside of the sum or inside
but you can try both to see which one works.
Or you could create an embedded with the same calculations
and place it inside the report and it may work.
Or you may not have a correct join on the table so it's
giving data to you twice.
Post by unknown
Here is my situation: I have a report that I have
separated into 2 groups of data.  I am taking group 2
data and using a computed field to give me a sum() of
the material costs for that group of data.  I then
want
Post by oceanandmtns
Post by unknown
to sum() all of those computed fields for a "group 1"
total.  When I write the equation to sum(compute_1 for
group 1) the value that is returned is exactly double
what it should be.  I have tried this several times
with
Post by oceanandmtns
Post by unknown
different equations and no luck.  I read online from a
Powerbuilder case in 2003, that it is not possible to
get an aggregate sum from an aggregate unless you add
some specific script to the report, which I do not know
how, nor want to do.  Has there been any changes with
Infomaker since then that allow me to sum up a summed
Post by unknown
value in a group?  Thanks, Dan
Paul Horan[Sybase]
2009-02-24 00:16:18 UTC
Permalink
How are you "separating" the data into two groups? Don't rely on its
positional order in the result set. Specify it explicitly.

Ex. say there's a column named "GroupThisDataBelongsIn" (or something less
intuitive) containing the value 1 or 2.
You can use that in your computes as follows:

sum(((if groupthisdatabelongsin = 1, 1, 0) * theData) for group 1)
--
Paul Horan[Sybase]
paulhoran.pbdjmagazine.com
Post by unknown
Here is my situation: I have a report that I have separated
into 2 groups of data. I am taking group 2 data and using a
computed field to give me a sum() of the material costs for
that group of data. I then want to sum() all of those
computed fields for a "group 1" total. When I write the
equation to sum(compute_1 for group 1) the value that is
returned is exactly double what it should be. I have tried
this several times with different equations and no luck. I
read online from a Powerbuilder case in 2003, that it is not
possible to get an aggregate sum from an aggregate unless
you add some specific script to the report, which I do not
know how, nor want to do. Has there been any changes with
Infomaker since then that allow me to sum up a summed value
in a group? Thanks, Dan
Loading...