Avg and QoQ

[GeertS]

Hi all,
Seems like I've got a problem with avg() and Query of Queries.
I've got fields like this:
100,null,100
Now if I do an average with SQL server, it ignores the null. But this is not the case for QoQ. Is there another way to tell the avg that it needs to ignore empty lines?
Thank you for your help
Geert

[Newsgroup_User]

GeertS wrote:
> Hi all,
> Seems like I've got a problem with avg() and Query of Queries.
> I've got fields like this:
> 100,null,100
> Now if I do an average with SQL server, it ignores the null. But this is not
> the case for QoQ. Is there another way to tell the avg that it needs to ignore
> empty lines?
> Thank you for your help
> Geert
>
I'm not sure, never tested this, but since ColdFusion does not actually
have a null, those may be empty strings in the QoQ. So maybe you can
add a where clause to filter out the empty records

WHERE averageField <> '' or something like that.

[Dan_Bracuk]

where averagefield * averagefield >= 0

[GeertS]

Yes, but I have multiple avg(), want to show avg of sold price and offered price in one query. So that's not an option I'm affraid.
Is there another possibility?
Thank you
Geert

[Newsgroup_User]

Not that I can think of. Would it be completely horrible to use
multiple QoQ's to do this, one for each field you want to average?

GeertS wrote:
> Yes, but I have multiple avg(), want to show avg of sold price and offered price in one query. So that's not an option I'm affraid.
> Is there another possibility?
> Thank you
> Geert
>

[GeertS]

To clarify
Field 1 Field 2
100 200
null 100
100 300
Do not want to delete line two completely.
Geert

[Newsgroup_User]

GeertS wrote:
> To clarify
> Field 1 Field 2
> 100 200
> null 100
> 100 300
> Do not want to delete line two completely.
> Geert

And if you willing to put up with the extra overhead, this could be done
pretty simply, albeit redundantly, with two QoQ blocks.

<cfquery name="fieldOneAvg" dbtype="query">
SELECT AVG(Field1) AS Average
FROM RecordSetVar
WHERE Field1 <> ''
</cfquery>

<cfquery name="fieldTwoAvg" dbtype="query">
SELECT AVG(Field2) AS Average
FROM RecordSetVar
WHERE Field2 <> ''
</cfquery>

<cfoutput>
#fieldOneAvg.Average#<br/>
#fieldTwoAvg.Average#
</cfoutput>

With a touch extra effort, once could probable create a UDF or CustomTag
that would allow for one reusable QoQ to be passed parameters and create
all the averages.