I need to find the average of the last five samples based on different locations but for some reason my results are only showing the last five samples NOT based on location. Here is my code:
SELECT AVG(result) as Average, sys_loc_code, sample_date
FROM
(SELECT s.sys_loc_code, s.sample_date from dt_sample s INNER JOIN (select top 5 sample_date, location FROM dt_sample s GROUP BY sys_loc_code, sample_date ORDER BY sample_date desc) AS hello ON hello.sample_date = s.sample_date and hello.sys_loc_code = s.sys_loc_code
group by s.sys_loc_code, s.sample_date order by s.sample_date