SQL Language to find the Median Value of a table

[kitkat123]

Hello!


I am having trouble finding the Median Value in SQL with the following table.

Letter	Dog	Weight
a	lab	50
b	pug	15
c	doc	10
d	lab	60
e	lab	65
f	pug	20
g	pug	19
h	doc	11
i	doc	14
j	lab	53
k	lab	70
l	doc	30
m	pug	24

The table is called TEST. I want to find the Median weight, grouping by dog, and I know that there is not a specific 'Median' function for this, but I can't seem to get the SQL language/formatting right in MS Access.
From my research so far, I know that I need to find the Min of the Top 50% and the Max of the Bottom 50% and then divide by 2 to get the Median, but I cannot for the life of me get this to work in Access.
I would really appreciate some help in how I can figure out the Median in and MS Access DB with SQl language (I know how to do this in Excel) !
Thank you!!
EDIT -- Here is a draft on how to SQL would probably go: SELECT MIN(Weight) FROM (SELECT TOP 50 PERCENT Weight FROM TEST order by Weight) ***somehow group this by dog
SELECT MAX(Weight) FROM (SELECT BOTTOM 50 PERCENT Weight FROM TEST order by Weight) ***somehow group this by dog Divide these by 2 and then VOILA
....would get the Medians for each dog group- can't figure out how to write this though!!

[ssanfu]

I Googled "calculate median access vba" (no quotes) and found a page of how to calculate median.


A sample that you might find easier to follow was created by Douglas Steele (MVP). Here is a link for it:
http://www.accessmvp.com/DJSteele/SmartAccess.html
The actual link on that site to download the zip file:
October 2005: An average column: I mean, what mode is your median?
http://www.accessmvp.com/DJSteele/Access/AA200510.zip

[orange]

I know you said SQL, but I knew I had seen a vba function. I found this in one of the demo databases I have, a routine called DMedian.
The code is a s follows:

'---------------------------------------------------------------------------------------
' Procedure : DMedian
' Author    : mellon (found on internet)
' Date      : 17/08/2015
' Purpose   : from https://msdn.microsoft.com/en-us/library/dd789431%28v=office.12%29.aspx?f=255&MSPPError=-2147217396
'---------------------------------------------------------------------------------------
'
Public Function DMedian( _
 ByVal strField As String, ByVal strDomain As String, _
 Optional ByVal strcriteria As String) As Variant

    ' Purpose:
    '     To calculate the median value
    '     for a field in a table or query.
    ' In:
    '     strField: the field.
    '     strDomain: the table or query.
    '     strCriteria: an optional WHERE clause to
    '                  apply to the table or query.
    ' Out:
    '     Return value: the median, if successful;
    '                   Otherwise, an Error value.

    Dim db As DAO.Database
    Dim rstDomain As DAO.Recordset
    Dim strSQL As String
    Dim varMedian As Variant
    Dim intFieldType As Integer
    Dim intRecords As Integer
    
    Const errAppTypeError = 3169
    
    On Error GoTo HandleErr

    Set db = CurrentDb()
    
    ' Initialize return value.
    varMedian = Null
    
    ' Build SQL string for recordset.
    strSQL = "SELECT " & strField & " FROM " & strDomain
    
    ' Only use a WHERE clause if one is passed in.
    If Len(strcriteria) > 0 Then
        strSQL = strSQL & " WHERE " & strcriteria
    End If
    
    strSQL = strSQL & " ORDER BY " & strField
    
    Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    ' Check the data type of the median field.
    intFieldType = rstDomain.Fields(strField).Type
    Select Case intFieldType
    Case dbByte, dbInteger, dbLong, _
      dbCurrency, dbSingle, dbDouble, dbDate
        ' Numeric field.
        If Not rstDomain.EOF Then
            rstDomain.MoveLast
            intRecords = rstDomain.RecordCount
            ' Start from the first record.
            rstDomain.MoveFirst
    
            If (intRecords Mod 2) = 0 Then
                ' Even number of records.
                ' No middle record, so move to the
                ' record right before the middle.
                rstDomain.Move ((intRecords \ 2) - 1)
                varMedian = rstDomain.Fields(strField)
                ' Now move to the next record, the
                ' one right after the middle.
                rstDomain.MoveNext
                ' And average the two values.
                varMedian = _
                  (varMedian + rstDomain.Fields(strField)) / 2
                ' Make sure you return a date, even when
                ' averaging two dates.
                If intFieldType = dbDate And Not IsNull(varMedian) Then
                    varMedian = CDate(varMedian)
                End If
            Else
                ' Odd number or records.
                ' Move to the middle record and return its value.
                rstDomain.Move ((intRecords \ 2))
                varMedian = rstDomain.Fields(strField)
            End If
        Else
            ' No records; return Null.
            varMedian = Null
        End If
    Case Else
        ' Non-numeric field; so raise an app error.
        Err.Raise errAppTypeError
    End Select

    DMedian = varMedian
    
ExitHere:
    On Error Resume Next
    rstDomain.Close
    Set rstDomain = Nothing
    Exit Function

HandleErr:
    ' Return an error value.
    DMedian = CVErr(Err.Number)
    Resume ExitHere
End Function

I created a table based on your data. I called it Dogs.

dogtype
Wght

doc
30

doc
14

doc
11

doc
10

lab
70

lab
65

lab
60

lab
53

lab
50

pug
24

pug
20

pug
19

pug
15

Then modified a test routine for the DMedian as below -modified for your Dogs data.

Sub testMedianWithGroup()

      Dim db As DAO.Database
      Dim rs As DAO.Recordset
10       On Error GoTo testMedianWithGroup_Error

20    Set db = CurrentDb
      'Set rs = db.OpenRecordset("Select aName, animalID from animal")
30    Set rs = db.OpenRecordset("Select distinct dogtype from dogs")
40    Debug.Print "dogtype " & vbTab & "Median weight"
50    Do While Not rs.EOF
60     Debug.Print rs!dogtype & "  " & vbTab & vbTab & DMedian("wght", "dogs", "dogtype = '" & rs!dogtype & "'")

70    rs.MoveNext
80    Loop

90       On Error GoTo 0
100      Exit Sub

testMedianWithGroup_Error:

110       MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure testMedianWithGroup of Module AWF_Related"
End Sub

The results are

dogtype     Median weight
doc         12.5
lab         60
pug         19.5

Median: mathematics : the middle value in a series of values arranged from smallest to largest