Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2007
    Posts
    11

    Unanswered: Query to find median

    Hi,

    I am trying to find the median of a column which holds the salary of employees. I have done this using the queries as shown below.

    ------------------------------------------------------------------------------------------------------------------------------------------------
    Qry:Union

    SELECT TOP 1 [FTE Salary] FROM HRMASTER WHERE [FTE Salary] in
    (SELECT TOP 50 PERCENT [FTE Salary] FROM HRMASTER ORDER BY [FTE Salary])
    ORDER BY [FTE Salary] DESC
    UNION SELECT TOP 1 [FTE Salary] FROM HRMASTER WHERE [FTE Salary] in
    (SELECT TOP 50 PERCENT [FTE Salary] FROM HRMASTER ORDER BY [FTE Salary] DESC)
    ORDER BY [FTE Salary];

    Q_Median

    SELECT Avg([FTE Salary]) AS Median
    FROM [Qry:Union];

    ------------------------------------------------------------------------------------------------------------------------------------------------
    Though the above query works fine its not optimised.

    But the requirement is I should be able to get the median by the [Directorate] and [band/designation]. I tried for avg (see query below) and it works fine but I would like get this work for median as well.

    ------------------------------------------------------------------------------------------------------------------------------------------------
    TRANSFORM Avg(HRMASTER.[FTE Salary]) AS [AvgOfFTE Salary]
    SELECT HRMASTER.Directorate, Avg(HRMASTER.[FTE Salary]) AS [Total Of FTE Salary]
    FROM HRMASTER
    GROUP BY HRMASTER.Directorate
    PIVOT HRMASTER.[Band/Grade];

    ------------------------------------------------------------------------------------------------------------------------------------------------

    Can you pls let me know the best way to get the median.?
    Is there a way to call a function from the query and display the required results on a datasheet?

    Any help would be greatly appreciated.

    Many thanks,
    Sowmya

    P.S: Sooner the better as I need to get this work by today. Thanks !!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by sowmya2103
    Q_Median

    SELECT Avg([FTE Salary]) AS Median
    FROM [Qry:Union];
    Just to point out that Avg() does not give you the median. Funnily enough it gives you the average!
    The median is defined as the middlemost value in an ordered resultset - for example

    0 1 2 2 4 5 6 17 81

    The median in the above resultset is highlighted in red.
    The average of these values is 13.11 (to 2dp).

    See the difference?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Posts
    11
    Thats right George !!

    But this query is calculating the average of the results obtained from the query Qry:Union

    Qry:Union gives 2 rows in case even number of rows are present and hence avg is calculated.

    Any idea how I can get this in a crosstab query grouped by Directorate and Grade?

    Many thanks !
    Sowmya

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wonder if google may have the answer

    ...seemingly it may

    ...amazing thing these internet search engines, mind you it did take a lot of skill to work out he search words were "median ms access sql"

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So to clarify, you're saying that the median of:

    0 1 99 100

    Is 50?
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on what dictionary definition (if any) you are using
    some state that its the middle value, and if there is an even number of values in the dataset then its the average of the adjacent values.

    so:
    0 1 99 100 median can be 50
    0 1 25 99 100 median is 25

  7. #7
    Join Date
    Oct 2007
    Posts
    11
    Thats right !!

    it would first sort and take the middle 2 values and calculate the average..

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You see... I would say that with an even number of items you have 2 medians
    But both answers are correct.

    Back to the question - you want to display the results of your median calculation/query on a form?
    George
    Home | Blog

  9. #9
    Join Date
    Oct 2007
    Posts
    11
    Thanks for ur reply..

    if u look at the crosstab query in SQL view, I need to get the median of salaries for each Grade grouped by Directorate.. The query is as below.. This gives Avg of the salaries but I would like a similar query for median.

    TRANSFORM Avg(HRMASTER.[FTE Salary]) AS [AvgOfFTE Salary]
    SELECT HRMASTER.Directorate, Avg(HRMASTER.[FTE Salary]) AS [Total Of FTE Salary]
    FROM HRMASTER
    GROUP BY HRMASTER.Directorate
    PIVOT HRMASTER.[Band/Grade];



    The table structure is something like this:

    ID Directorate Band/Grade Salary
    1 Business Solutions A 1000
    2 Customer Supp B 500
    3 Business Solutions A 4500
    4 Business Solutions A 3500
    5 Customer Supp B 5000


    Many Thanks,
    Sowmya

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    You see... I would say that with an even number of items you have 2 medians
    But both answers are correct.....
    i doubt very much that BOTH answers are correct, castingmy mind back all those years ago to my sessions on quantitatve methods the median represent the middle value of the data set.. NOT the middle two values, so if you have an even number of values in the dataset then you use the average of the middle two values, not either or depending on which value best meets your criteria


    if you are going to play so fast and loose with statistical methods.. fair enough



    btw assuming that you do play fast and loose with statistical methods.... have you though of a career shift into say politics, or corporate PR

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I studied Statistics at school
    Quote Originally Posted by healdem
    the median represent the middle value of the data set.
    Exactly, in an even datasets ther are two middle values.
    I still believe both answers are correct, casting my memory back to the last time I did this stuff.
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2007
    Posts
    33
    The Median Value:
    Originally Posted by healdem
    the median represent the middle value of the data set.
    <>

    The Average Value: represent the sum of values Divided by the number of values


    1, 3, 5, 7, 9; Median = 5 ; Average = 5
    1, 2, 4, 10, 13; Median = 4 ; Average = 6

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree
    .. but your examples are for datasets with odd numbers
    for a dataset with an even number of items then the median is the middle / average of the two middle values

    eg
    1, 2, 4, 10, 13, 15 median 7 (4+10)/2 av 7.5

  14. #14
    Join Date
    Jun 2007
    Posts
    33
    Exactly


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •