Query to look day / month (and not year) then produce an average

[Scott488]

Apologies if this isn't explained particularly well.

I have a table containing dates in column A and a number in column B. What I would like to do is average the numbers in column B based on the day and month in column A and for it to show the latest full date range.

So in the example below, the response would be 01/01/2020 and 12.5

01/01/2017	10
01/01/2018	15
01/01/2019	15
01/01/2020	10

 

[plog]

This SQL will produce what you want:

SELECT Max(YourDateField) AS LatestDate, Avg(YourNumberField) AS AverageOfYourNumberField
FROM YourTableName
GROUP BY Day([YourDateField]), Month([YourDateField]);

 

[Scott488]

Thank you, this worked perfectly

 

[Scott488]

As often happens, we think we know what we want then when a solution is provided we adjust our requirements

The above solution works as expected, but having looked at the results, I would like to exclude the day/month that falls is the current year from the calculations.

 

[plog]

Give it a shot, see what you come up with and then post back here if it doesn't work.

 

[arnelgp]

SELECT Max(YourDateField) AS LatestDate, Avg(YourNumberField) AS AverageOfYourNumberField
FROM YourTableName
WHERE Year(YourDateField) <> Year(Date)
GROUP BY Day([YourDateField]), Month([YourDateField]);