AVG Query Question

[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

 

[Duane Hookom]

SELECT ContractNumber, ContractValue,
(Select Avg(ContractValue) FROM tblContracts) as AvgValue
FROM tblContracts

 

[Guest]

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

 

[G. Vaught]

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.