Average two records

[NHCPA]

I have a query that has the following 3 fields

Dept, Sequence #, Amount

I am trying to add a field (in this query or a new one) that will average the amount per record with the amount of the record with the previous sequence #.

Thanks for any help.

 

[lupins46]

What do the sequence numbers look like?

 

[NHCPA]

Numbers starting from 1 up to approx 100 now.

 

[Golom]

Select ((A.fld + (Select B.Fld From tbl As B
                 Where B.Seq = 
                 (Select Max(Seq) From tbl As C
                  Where C.Seq < A.Seq) ) ) / 2) As AvgVal

From tbl As A

Where A.Seq > (Select MIN(Seq) From tbl)

Hope I got the parens matched and in the right places.

 

[NHCPA]

Thanks Golom for your time.

I tried using it, but I got a error message "Cant have an aggregate function in a Where clause".

Please advise. Thanks in advance.

 

[Golom]

Strange. Here's a table names "PositionSets" that I have in my test Database
[tt]
UnitName PosZ
TU1 601
TU2 698
TU3 704
[/tt]
And here's the SQL I ran

Select ((A.PosZ + (Select B.PosZ  From PositionSets As B
                 Where B.UnitName = 
                 (Select Max(UnitName) From PositionSets As C
                  Where C.UnitName < A.UnitName) ) ) / 2) As AvgVal

From PositionSets As A

Where A.UnitName > (Select MIN(UnitName) From PositionSets)

which resulted in
[tt]
AvgVal
649.5
701
[/tt]
Can you post the SQL that you tried?

 

[NHCPA]

Thank you again Golom

I have a table 'table1' which contains the following:

Dept Amount ID
Dept1 23 41
Dept2 28 41
Dept1 23 42
Dept2 27 42
Dept1 23 43
Dept2 30 43

and a query:

SELECT ((A.Amount + (Select B.Amount from table1 as B
Where B.ID = (Select Max (ID) From table1 As C Where C.ID < A.ID))) / 2) AS AvgVal
FROM table1 AS A
WHERE A.ID > (Select MIN (ID) From table1);

and am currently getting the error (I had it a little mixed up before):

"At most one record can be returned by this subquery".

 

[PHV]

And this ?
SELECT A.Dept, ((A.Amount + (Select B.Amount from table1 as B Where B.Dept=A.Dept And B.ID = (Select Max (ID) From table1 As C Where C.ID < A.ID And C.ID=A.Dept))) / 2) AS AvgVal
FROM table1 AS A
WHERE A.ID > (Select Min(D.ID) From table1 D WERE D.Dept=A.Dept);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244