Averaging and Aggregate functions

[DavidQMP]

Hi,



I'm trying to average 5 queries, however when I use a simple avg(query1+query2+query3) etc i get an error message regarding aggregates. I would use a simple /3 at the end instead however sometimes there is not a value in one of the queries, so it doesn't give the correct result. It there a simple formula to ignore empty values - obviously not NZ because that would 0 them.

Could anyone help?

Thanks

[orange]

Tell us about the 5 queries, or show us some SQL.

[DavidQMP]

So query 1 groups the type of property to give an average value, i.e.

detached 100k
semi-detached 90k

i've then linked this back to another query using this property type.

So i have a unique reference number that shows what the average value of a detached house is.

The rest of the queries work in a similar way and are all linked back like this, adding them together and dividing by 5 works in the main query. But not if one of my sub queries has a null value in it, because it artificially lowers the averaged result, when i want to discount null values.

[orange]

In your query(ies) you want to exclude NULL values..

where  NOT  yourFieldValue  IS NULL

(should work in the query SQL criteria)

Show us some SQL and identify the field(s) that may be NULL