AVG not working with DatePart

[rbennion]

I hope i can explain this as clearly as possible. I have a subquery (it's technically a second query because i couldn't figure out the syntax for doing a nested query) that is giving me a percent difference between two fields. See code below.

SELECT General.AACVPR_ID, General.Last_Name, General.Program_Completion, General.Discharge_Date, Intake.PeakMETs, Discharge.PeakMETs, Round((Discharge.PeakMETs-Intake.PeakMETs)/Intake.PeakMETs,2) AS PercentChangeInPeakMETs
FROM (Intake INNER JOIN Discharge ON Intake.AACPVR_ID = Discharge.AACVPR_ID) INNER JOIN [General] ON Intake.AACPVR_ID = General.AACVPR_ID
WHERE (((Intake.PeakMETs) Is Not Null) AND ((Discharge.PeakMETs) Is Not Null) AND ((General.[Program_Completion])=Yes));


this returns the following:

AACVPR_ID	Last_Name	Program_Completion	Discharge_Date	Intake.PeakMETs	Discharge.PeakMETs	PercentChangeInPeakMETs
f8a13ac3-2cb7-447a-9c2f-45e6407be18b	name	Yes	12/5/2014	4.1	5	0.22
5ec64975-4d6b-439b-98fc-46f5962a1799	name	Yes	3/7/2014	4.9	4	-0.18
b21a939d-de3b-4330-b23d-47a785bb3643	name	Yes	6/12/2015	4.7	3.4	-0.28
06e5a5a3-a305-409f-860f-47b46f926cbe	name	Yes	2/24/2014	1.8	2.1	0.17
1e2f8685-ef5a-4f5c-a640-00442d2962ff	name	Yes	1/30/2015	4.2	7.9	0.88
9ae5c15e-0aa0-4551-b544-00ad95e9ea15	name	Yes	3/27/2017	5.7	10.6	0.86

When I say "subquery", the above query is what I'm referring to. From there I have a separate query that builds on this to average the "PercentChangeInPeakMETs" column by datepart. See code Below:

SELECT DatePart ("q", [Discharge_Date]) AS Qtr, DatePart ("yyyy", [Discharge_Date]) AS [Year], ROUND(AVG(PercentChangeInPeakMETs), 2) AS AvgPercentChangeInPeakMETs
FROM [12_BUILDER_%ChangeInPeakMETs]
GROUP BY DatePart ("q", [Discharge_Date]), DatePart ("yyyy", [Discharge_Date]);

This returns:

Qtr	Year	AvgPercentChangeInPeakMETs
1	2014	0.13
1	2015	0.3
1	2016	0.47
1	2017	0.65
2	2013	0.26

I also have the same exact query but with DatePart "m" for month. See below.

Year	Month	AvgPercentChangeInPeakMETs
2013	5	0.38
2013	6	0.03
2014	2	0.25
2014	3	0.0725
2014	4	0.55
2014	5	1.1171
2014	6	0.796
2014	7	0.1388

The issue I'm having is when I run the same DatePart query but for "m" month. they do not return averages that coincide with the quarters. qtr 1 of 2014 shows an avg of 13% while the 2014 months of feb (.25) and march (.0725) should be an avg of 16%. Why would this be? They are both building from that same builder query. I have manually gone through the results and I know that the monthly average is correct, but for some reason the quarter one is off just a bit.



Sorry this is long winded but if anyone has an answer it would be extremely helpful!

[John_G]

You should not expect to get the same averages, mathematically, because population sizes are probably different. This is best illustrated by an example.

Suppose you have 15 numbers: 10, 10, 10, 10, 10, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1

A) The average of all of them together is 60 / 15 = 4

B) The average of the first 5 is 50/5 = 10

c) The average of the last 10 is 10/10 = 1

BUT - the average of the averages B and C = (10 + 1)/2 = 11/2 = 5.5

There is a big difference between 4 and 5.5, and your differences are due to the same thing. an "Average of Averages" has to be regarded carefully with respect to what it is really telling you.

Here is a little routine I wrote to demonstrate:

Sub Averages()
  Dim Numbers(100) As Integer, J As Integer
  Dim sum1 As Single, sum2 As Single, sum3 As Single, sum4 As Single, SumAll As Single, SumAverage As Single
  SumAll = 0
  For J = 1 To 100
    Numbers(J) = Int(Rnd * 100)
    SumAll = SumAll + Numbers(J)
  Next J
  Debug.Print "Average of all 100 is " & SumAll / 100#

  sum1 = 0
  sum2 = 0
  sum3 = 0
  sum4 = 0
  SumAverage = 0
  For J = 1 To 25
    sum1 = sum1 + Numbers(J)
  Next J
  SumAverage = SumAverage + sum1 / 25
  Debug.Print "Average  1 - 26 = " & sum1 / 25
  For J = 26 To 50
    sum2 = sum2 + Numbers(J)
  Next J
  SumAverage = SumAverage + sum2 / 25
  Debug.Print "Average  26 - 50 = " & sum2 / 25
  For J = 51 To 75
    sum3 = sum3 + Numbers(J)
  Next J
  SumAverage = SumAverage + sum3 / 25
  Debug.Print "Average  51 - 75 = " & sum3 / 25
  For J = 76 To 100
    sum4 = sum4 + Numbers(J)
  Next J
  SumAverage = SumAverage + sum4 / 25
  Debug.Print "Average  76 - 100 = " & sum4 / 25
  Debug.Print "Average of averages for 4 equal populations = " & SumAverage / 4
  Debug.Print
  '============================================
  Debug.Print "Average of all 100 is " & SumAll / 100#
  sum1 = 0
  sum2 = 0
  sum3 = 0
  sum4 = 0
  SumAverage = 0
  For J = 1 To 10
    sum1 = sum1 + Numbers(J)
  Next J
  SumAverage = SumAverage + sum1 / 10
  Debug.Print "Average  1 - 10 = " & sum1 / 10
  For J = 11 To 30
    sum2 = sum2 + Numbers(J)
  Next J
  SumAverage = SumAverage + sum2 / 20
  Debug.Print "Average  11 - 30 = " & sum2 / 20
  For J = 31 To 60
    sum3 = sum3 + Numbers(J)
  Next J
  SumAverage = SumAverage + sum3 / 30
  Debug.Print "Average  31 - 60 = " & sum3 / 30
  For J = 61 To 100
    sum4 = sum4 + Numbers(J)
  Next J
  SumAverage = SumAverage + sum4 / 40
  Debug.Print "Average  61 - 100 = " & sum4 / 40
  Debug.Print "Average of averages for 4 non-equal populations = " & SumAverage / 4
  Debug.Print
End Sub

Copy that into a module; when you run it you will can see what I mean. If the populations of the groups (I used 4) are ALL the same, then the "averages of averages" is the same as the average of the whole, otherwise it isn't.

[rbennion]

dude, you're a genius. lol. I have been racking my brain looking for a SQL problem when it's actually just a math problem. thanks for the thorough explanation as well.

thanks again