How to find the average of top N rows based on date and location group in SQL?
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
Best Answer
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
