question

barena avatar image
barena asked

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
groupingtopavgorder
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

barena avatar image barena commented ·

This worked! Thank you soooo much!

0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist barena commented ·

Glad to help :)

0 Likes 0 ·

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.