Average in Query

[Guest]

I have three tables, joined with a common primary key. The main table has a
field of 'total property value' The query runs as I expect with the values.
However, I want to add a new field that gives and average of the 'total
property value' collumn of all values. The return on my query is about
110,000 records which is to large to try and export to Excel and average.

 

[Guest]

I forgot to add the SQL

SELECT dbo_Property.P_QuickRefID, dbo_Property.P_PropertyNumber,
dbo_Owner.O_OwnerName, dbo_Exemption.E_ExemptionStatusCode,
dbo_Exemption.E_ExemptionTypeCode, dbo_Property.P_TotalPropMktValue
FROM (dbo_Property INNER JOIN dbo_Owner ON (dbo_Property.P_TaxYear =
dbo_Owner.O_TaxYear) AND (dbo_Property.P_PropertyID =
dbo_Owner.O_PropertyID)) INNER JOIN dbo_Exemption ON (dbo_Owner.O_PartyID =
dbo_Exemption.E_PartyID) AND (dbo_Owner.O_TaxYear = dbo_Exemption.E_TaxYear)
AND (dbo_Owner.O_PropertyID = dbo_Exemption.E_PropertyID)
WHERE (((dbo_Exemption.E_ExemptionStatusCode)="A") AND
((dbo_Exemption.E_ExemptionTypeCode)="HS") AND
((dbo_Property.P_TaxYear)=2005) AND ((dbo_Property.P_PropertyStatusCode)="a")
AND ((dbo_Exemption.E_TaxYear)=2005) AND
((dbo_Property.P_TaxingUnitGroupDesc) Like "*w30*"))
ORDER BY dbo_Owner.O_OwnerName;

 

[James McNellis]

JLD--

Have you tried SELECT
SUM(dbo_Property.P_TotalPropMktValue)/COUNT(dbo_Property.P_TotalPropMktValue
) AS average? Make sure if you want to group the averages by a field that
you use the GROUP BY keyword.

HTH--

James.

I forgot to add the SQL

SELECT dbo_Property.P_QuickRefID, dbo_Property.P_PropertyNumber,
dbo_Owner.O_OwnerName, dbo_Exemption.E_ExemptionStatusCode,
dbo_Exemption.E_ExemptionTypeCode, dbo_Property.P_TotalPropMktValue
FROM (dbo_Property INNER JOIN dbo_Owner ON (dbo_Property.P_TaxYear =
dbo_Owner.O_TaxYear) AND (dbo_Property.P_PropertyID =
dbo_Owner.O_PropertyID)) INNER JOIN dbo_Exemption ON (dbo_Owner.O_PartyID =
dbo_Exemption.E_PartyID) AND (dbo_Owner.O_TaxYear = dbo_Exemption.E_TaxYear)
AND (dbo_Owner.O_PropertyID = dbo_Exemption.E_PropertyID)
WHERE (((dbo_Exemption.E_ExemptionStatusCode)="A") AND
((dbo_Exemption.E_ExemptionTypeCode)="HS") AND
((dbo_Property.P_TaxYear)=2005) AND ((dbo_Property.P_PropertyStatusCode)="a")
AND ((dbo_Exemption.E_TaxYear)=2005) AND
((dbo_Property.P_TaxingUnitGroupDesc) Like "*w30*"))
ORDER BY dbo_Owner.O_OwnerName;

average.

 

[Guest]

What I add this to a new field it just returns the same info that is in the
total market value. It does not averate the entire collumn. In the 'total'
row for this field I chose 'expression'.