Finding single Average of multiple columns

[ppatel2]

So I'm trying to get the one mean value of three columns from my table. I had to use the three columns because the intial SQL statement uses an outer join on a single table three times to pull three different values. I'll Provide an example of the data below:

C1 C2 C3
Row 1 200
Row 2 250
Row 3 210
Row 4 290
Row 5 280
Row 6 300


ID LIKE THIS INFORMATION TO BE AVERAGED INTO ONE FIELD INSTEAD OF THREE SEPERATE AVERAGES. THE BLANK AREAS ARE NULL VALUES.

 

[lbass]

Create a formula like this:

whileprintingrecords;
numbervar cnt;
numbervar vals;

if isnull({table.C1}) then
cnt := cnt else
cnt := cnt + 1;
if isnull({table.C2}) then
cnt := cnt else
cnt := cnt + 1;
if isnull({table.C3}) then
cnt := cnt else
cnt := cnt + 1;

vals := vals +
(
if isnull({table.C1}) then 0 else {table.C1}
) +
(
if isnull({table.C2}) then 0 else {table.C2}
) +
(
if isnull({table.C3}) then 0 else {table.C2}
);

Place this in the detail section and then in the report footer, add a formula like this:

whileprintingrecords;
numbervar vals;
numbervar cnt;
vals/cnt

-LB

 

[synapsevampire]

You might also use a UNION ALL qeury to combine them all into one field using an Add Command or a View/SP on the database, but you need to post meaningful technical information such as your software version and the database being used.

The advantage to building out an intelligent datasoruce si that it resolves long term rather than a front end solution for an atomic process such as Crystal.

-k