AVG Query Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

What would be the SQL to return the following ?

I have a table called tblContracts. Each Contract has a ContractValue.
For each Contract I want to return the ContractNumber, ContractValue & the
Average of all the ContractValues. See example:

ContractNumber ContractValue AvgContractValue
-------------------------------------------------------
1 10.00
10.00
2 15.00
10.00
3 5.00
10.00

I tried:

SELECT ContractNumber, ContractValue, Avg(ContractValue)
FROM tblContracts

But I can an error "You tried to execute a Query that does not include the
specified expression 'ContractNumber' as part of an aggregate function"

Any help would be greatly appreciated.

Thank you,
Jeff
 
SELECT ContractNumber, ContractValue,
(Select Avg(ContractValue) FROM tblContracts) as AvgValue
FROM tblContracts
 
Hi Duane,

Thank you, that helped.

I am sorry now I have another question perhaps you would be kind enough to
help me with. Along the same logic...

Now if I have tblContracts & have a Field StartDate and End Date & then want
to have the number days in between the Start and End Date and the Average of
those. So for Example:

Contract# StartDate EndDate DaysInBetween AvgDays
-----------------------------------------------------------------
1 1/1/05 1/15/05 10 15

2 2/3/05 2/27/05 24 15
3 1/8/05 1/19/05 11 15

I tried based on your answer before:

SELECT ContractNumber,StartDate, EndDate,
DateDiff("d",StartDate,EndDate) AS DaysInBetween,
(Select Avg(DateDiff("d",StartDate,EndDate)) FROM tblContracts) as AvgDays
FROM tblContracts

But I get a negative # for the Avg, no where near correct.

Any help would be appraciated again.

Thank you,
Jeff
 
Be sure you subtract the End Date from the Start Date and not vice versa,
which I think your formula is doing. Then you need to Avg([DaysInBetween])
As AvgDays. You should not have to invoke the DateDiff with Avg.
 
Back
Top