question

barena avatar image
barena asked

How to find avg of n number of rows by sample date?

I need to find the average of the last five samples from the past 5 records (by date)

Here's what I got so far:

select avg (result_numeric)as avgresult
from
(select top 5 sample_date, sample_code, result_numeric
FROM dt_sample
inner join dt_result r 
on s.facility_id = r.facility_id and r.test_id=t.test_id
where s.facility_id = 140 and sample_date is not null
order by sample_date desc) as sq 

I am getting this erorr:

Msg 4104, Level 16, State 1, Line 8

The multi-part identifier "s.facility_id" could not be bound.

Msg 4104, Level 16, State 1, Line 8

The multi-part identifier "t.test_id" could not be bound.

Msg 4104, Level 16, State 1, Line 9

The multi-part identifier "s.facility_id" could not be bound.

topaverage
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

You have not defined what 's' and 't' are. I presume they are table aliases, and maybe s should be dt_sample, but I've no idea what to suggest t might be

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.