Group By in a Median Query

M

mab9

Registered User.
Local time
Today, 10:46
Joined
Oct 25, 2006
Messages
63
I've gotten the medianf fuction that is pasted on these boards working in the attached db example. The problem I am running into now is the medianf function (correctly) takes the median for an entire table at once. My dataset example looks like:

type num
t1 1
t1 2
t1 3
t1 6
t1 6
t1 100
t2 1
t2 2
t2 200

I'm trying to end up with median t1 = 4.5, t2 = 2. With the way that function works, it sets them both to 3 which is the median of the entire set. I have the query saved in here already setup so far. Any ideas on getting the grouping to work properly?
 

Attachments

Your Function MedianF doesn't contain a filter for the group. This means that it will perform the action for all records that match the current criterea.

Because I don't know where you call the function in your database I changed your function to:

Code: 
Function MedianF(pTable As String, pfield As String, [COLOR="Blue"]Optional pgroup As String[/COLOR]) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight"[COLOR="blue"],[Value of Grouping field][/COLOR]) <enter>
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************

Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single

[COLOR="blue"]If Len(pgroup) > 0 Then
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE type= '" & pgroup & "' and " & pfield & ">0 Order by " & pfield & ";"
Else [/COLOR]
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
[COLOR="blue"]End If[/COLOR]
Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function
 
Perfect, got it working. Thanks!
 
MedianF Grouping

I was able to get your function to work but I noticed that it only returns the value of one type (t1, t2) in the table. Is there a function to have the query return the median value for each type and show ALL median values by type in the same query (refer to original sample date in this string)?
 
voslica said:
I was able to get your function to work but I noticed that it only returns the value of one type (t1, t2) in the table. Is there a function to have the query return the median value for each type and show ALL median values by type in the same query (refer to original sample date in this string)?
Click to expand...

By grouping Your query you should be able to get the results you want.
Something like this could work.
Code: 
SELECT YourTableName.Type, First(MedianF("YourTableName","num",[type])) AS GroupMedian, First(MedianF("YourTableName","num")) AS TotalMedian
FROM YourTableName
GROUP BY YourTableName.Type;
 
This is great. Thanks! Is is possible to group by multi levels in the query?
 
voslica said:
This is great. Thanks! Is is possible to group by multi levels in the query?
Click to expand...

You have to change the function to be able to group on multi levels. Depending on how many fields you want to use for grouping you need to ad these in the function like I did for the first group.
 
Can you provide an example in how to add additional grouping levels (to the Function Module and to the Query)
 
Hi,

I am trying to get this to work in a query but when I run the query I get a Run-time error '3061': Too few parameters. Expected 1.

I am a VBA newbie, what do you put in the function for the "pgroup" string? From the above dataset would you put in the actual type value, i.e. "T1". I think I understand that but not sure how to modify the code to get medians for every type in the output table. Any help is appreciated, thanks
 
OK I got this to work, but where do you put the SQL code calculates median for all types? Is this a seperate query?
 
PeterF said:
Your Function MedianF doesn't contain a filter for the group. This means that it will perform the action for all records that match the current criterea.

Because I don't know where you call the function in your database I changed your function to:

Code: 
Function MedianF(pTable As String, pfield As String, [COLOR="Blue"]Optional pgroup As String[/COLOR]) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight"[COLOR="blue"],[Value of Grouping field][/COLOR]) <enter>
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************

Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single

[COLOR="blue"]If Len(pgroup) > 0 Then
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE type= '" & pgroup & "' and " & pfield & ">0 Order by " & pfield & ";"
Else [/COLOR]
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
[COLOR="blue"]End If[/COLOR]
Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function
Click to expand...


Excellent peice of code. I altered this slightly to calculate mean in my queries. Thanks for the great code!
 
I copied the first fuction and wrote the query exactly as shown just changing the names of my variables and it gives me a run time error 3061 : Too few parameters Expected 1 :confused:
 
I get "Run-time error 3131 Syntax error in FROM clause.

Any idea why?
This is how I call the function:
Code: 
SELECT [table].[name], [table].number, MedianF("table","number",[name]) AS median
FROM [table];
I also created the module as "option compare database" and then tried "option explicit"

Neither case worked.

Thanks!
PeterF said:
Your Function MedianF doesn't contain a filter for the group. This means that it will perform the action for all records that match the current criterea.

Because I don't know where you call the function in your database I changed your function to:

Code: 
Function MedianF(pTable As String, pfield As String, [COLOR=Blue]Optional pgroup As String[/COLOR]) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight"[COLOR=blue],[Value of Grouping field][/COLOR]) <enter>
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************

Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single

[COLOR=blue]If Len(pgroup) > 0 Then
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE type= '" & pgroup & "' and " & pfield & ">0 Order by " & pfield & ";"
Else [/COLOR]
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
[COLOR=blue]End If[/COLOR]
Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function
Click to expand...
 
Last edited:
SELECT
.[name],
.number, MedianF("table","number",[name]) AS median
FROM
;
Click to expand...

Suggests you're sending the literal strings "Table" and "Number" to the function rather than the Tablename and field.

"Syntax error in the FROM clause" generally tends to mean that the table name is spelt wrong.
 
Thanks, but no. It is spelt correctly. I used "table", "number" and "name" just as examples.
 
Thanks for your reply, but no. Both table and field name are spelt correctly.

Any other suggestions?
 
I figured this out, the

Code: 
[/COLOR]
[COLOR=black]WHERE [COLOR=blue]type[/COLOR]= '" & pgroup & "'[/COLOR]
[COLOR=black]

should be changed to

Code: 
[/COLOR]
[COLOR=black]WHERE [COLOR=blue]fieldname[/COLOR]= '" & pgroup & "'[/COLOR]
[COLOR=black]
where fieldname is the name of the field you are grouping by.

Additionally, to group by more than one field, I modified it again:

Code: 
Function MedianF(pTable As String, pfield As String, Optional pgroup As String, Optional pgroup2 As String) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight",[Value of Grouping field]) <enter>
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************
Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single
If Len(pgroup) > 0 Then
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE [COLOR=black]fieldname1[/COLOR]= '" & pgroup & "[COLOR=blue][COLOR=black]'[/COLOR] and fieldname2='" & pgroup2 & "'[/COLOR] and " & pfield & ">0 Order by " & pfield & ";"
Else 
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
End If
Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function

Hope this helps clear up some of the issues
 
Sorry for resurrecting this from the dead, but it might help anyone searching for the function (as I was)...

Code: 
[COLOR=black]WHERE [COLOR=blue]type[/COLOR]= '" & pgroup & "'[/COLOR]

should be changed to

Code: 
[COLOR=black]WHERE [COLOR=blue]fieldname[/COLOR]= '" & pgroup & "'[/COLOR]

where fieldname is the name of the field you are grouping by.

Additionally, to group by more than one field, I modified it again:

Code: 
Function MedianF(pTable As String, pfield As String, Optional pgroup As String, Optional pgroup2 As String) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight",[Value of Grouping field]) <enter>
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************
Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single
If Len(pgroup) > 0 Then
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE [COLOR=black]fieldname1[/COLOR]= '" & pgroup & "[COLOR=blue][COLOR=black]'[/COLOR] and fieldname2='" & pgroup2 & "'[/COLOR] and " & pfield & ">0 Order by " & pfield & ";"
Else 
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
End If
Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
 
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function

Hope this helps clear up some of the issues

also, to call the function in a query, you need to make it aggregate (group by), including the fieldname1 and fieldname2 as total: "group by", and the medianF field as total: first

so the SQL will be:

Code: 
SELECT qryName.fieldname1, qryName.fieldname2, [COLOR=blue]First[/COLOR](medianf("qryName","[Seconds]",[fieldname1],[fieldname2])) AS [Median Value]
 
FROM qryName
 
[COLOR=blue]GROUP BY qryName.fieldname1, qryName.fieldname2[/COLOR];
 
You must log in or register to reply here.

Similar threads

A
Solved SQL query advise for inventory item and set
Replies
11
Views
1,201
atzdgreat
A
H
Solved Finding names from table of not duplicated rows
Replies
11
Views
445
Pat Hartman
P
G
  • Locked
Creating all possible Descriptions & Codes
2
Replies
22
Views
1,163
The_Doc_Man
The_Doc_Man
AOB
Sort Order clean-up query
Replies
7
Views
884
Pat Hartman
P
C
Query Help on "Lookup" Type Function
Replies
12
Views
300
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom