This content has been marked as final. Show 6 replies
> Hi all,
> Seems like I've got a problem with avg() and Query of Queries.
> I've got fields like this:
> 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
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.
where averagefield * averagefield >= 0
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?
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?
> 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
Field 1 Field 2
Do not want to delete line two completely.
> To clarify
> Field 1 Field 2
> 100 200
> null 100
> 100 300
> Do not want to delete line two completely.
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
WHERE Field1 <> ''
<cfquery name="fieldTwoAvg" dbtype="query">
SELECT AVG(Field2) AS Average
WHERE Field2 <> ''
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.