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

Scott488

Registered User.
Local time
Today, 19:27
Joined
Nov 6, 2015
Messages
16
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/201710
01/01/201815
01/01/201915
01/01/202010
 

plog

Banishment Pending
Local time
Today, 14:27
Joined
May 11, 2011
Messages
11,611
This SQL will produce what you want:

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

Scott488

Registered User.
Local time
Today, 19:27
Joined
Nov 6, 2015
Messages
16
Thank you, this worked perfectly (y)
 

Scott488

Registered User.
Local time
Today, 19:27
Joined
Nov 6, 2015
Messages
16
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

Banishment Pending
Local time
Today, 14:27
Joined
May 11, 2011
Messages
11,611
Give it a shot, see what you come up with and then post back here if it doesn't work.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:27
Joined
May 7, 2009
Messages
19,169
Code:
SELECT Max(YourDateField) AS LatestDate, Avg(YourNumberField) AS AverageOfYourNumberField
FROM YourTableName
WHERE Year(YourDateField) <> Year(Date)
GROUP BY Day([YourDateField]), Month([YourDateField]);
 

Users who are viewing this thread

Top Bottom