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
(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
Answer by Magnus Ahlkvist ·
The reason you only get five, is that in your derived table (called hello in your code) you're just doing TOP5, but with no WHERE clause correlating to the outer query (against dt_sample). However, if you'd try to correlate the inner query to the outer query, you'll get an error message, because of the all-at-once evaluation of the FROM clause (including the JOIN clause).
In this scenario, the APPLY operator is your friend, which conceptually works on a per-row-basis from the outer query and allows for correlating outer and inner query.
I also see some logical things. If you want 5 most recent samples per location, you can't group by sample-date, that will give you the average per sample which probably isn't what you want.
The code here might work, and if it doesn't, you have to clarify your requirements with sample data and expected results.
SELECT AVG(s_outer.result) as Average, s_outer.sys_loc_code FROM dt_sample as s_outer CROSS APPLY ( SELECT TOP 5 * FROM dt_sample as s_inner WHERE s_inner.syc_loc_code = s_outer.sys_loc_code ORDER BY sample_date desc ) AS hello GROUP BY s_outer.sys_loc_code ORDER BY s_outer.sys_loc_code