I have a table which contains a book and then multiple prices about the book (this is a highly simplified sample):
ID BOOK PRICE 1 BOOK1 10 2 BOOK1 15 3 BOOK1 12 4 BOOK2 8 5 BOOK2 2
I am easily calculating the average, but there must be a nice way to calculate the median?
Current SQL:
SELECT DISTINCTROW Books.BOOK, Avg(Books.PRICE) AS [Avg Of PRICE]
FROM Books
GROUP BY Books.BOOK;
Results:
BOOK Avg Of PRICE BOOK1 12.3333333333333 BOOK2 5
There is no Median in Jet SQL, unless it has been added for 2007, but here is an idea of how to get one. You will need ...
Some SQL ...
SELECT Statistics.Month, Sum(([SentTo])) AS [Sum Sent], fMedian("Statistics","Month",[Month],"SentTo") AS [Median Sent]
FROM Statistics
GROUP BY Statistics.Month;
And a User Defined Function (UDF).
Function fMedian(SQLOrTable, GroupFieldName, GroupFieldValue, MedianFieldName)
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset(SQLOrTable, dbOpenDynaset)
If IsDate(GroupFieldValue) Then
GroupFieldValue = "#" & GroupFieldValue & "#"
ElseIf Not IsNumeric(GroupFieldValue) Then
GroupFieldValue = "'" & Replace(GroupFieldValue, "'", "''") & "'"
End If
rs1.Filter = GroupFieldName & "=" & GroupFieldValue
rs1.Sort = MedianFieldName
Set rs = rs1.OpenRecordset()
rs.Move (rs.RecordCount / 2)
If rs.RecordCount Mod 2 = 0 Then
varMedian1 = rs.Fields(MedianFieldName)
rs.MoveNext
fMedian = (varMedian1 + rs.Fields(MedianFieldName)) / 2
Else
fMedian = rs.Fields(MedianFieldName)
End If
End Function
From: LessThanDot Wiki