SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Mode/Medium Question

    I do not know if I can do this in a SQL statement or this will require some php code to crunch the numbers. Basically I need to know the Mode and the Medium of the results in a mysql table.

    Basically I have a survey tool. The survey tool searches between a start date and a end date. All the results from the questions are stored in one table. From there I need to find the Mode (Response most picked) and the Medium (In the middle, not average) of all the results found in that time frame.

    Can anyone help me on this? So I need to know what value is being selected the most and what value is in the middle (Not average)

    Thanks
    Silly

  2. #2
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The median I can do for you right now, you'll have to ask someone else about the mode:
    PHP Code:
    $countentries="SELECT * from table order by Value Desc";
    $countentries2=mysql_query($countentries);
    $num=mysql_num_rows($countentries2);
    $num=$num/2;
    $median=0;
    if(
    $num%2=0)
    {
    $get="SELECT * from table order by value desc limit $num,2";
    $get2=mysql_query($get) or die("dead" );
    while(
    $get3=mysql_fetch_array($get2))
    {
    $median=$median+$get3[value]
    }
    $median=$median/2;
    }
    else
    {
    $get="SELECT * from table order by value desc limit $num,1"$get2=mysql_query($get) or die("dead" );
    $get3=mysql_fetch_array($get2);
    $median=$get3[value];
    }
    echo 
    "$median"
    Yeah, thats my complicated way of doing median.

    With mode you have to select distinct value or table, loop through that, and count the number of rows with each distance value in the value field.

  3. #3
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually to get the modal number you'd do:

    SELECT number, COUNT(*) FROM table GROUP BY number ORDER BY COUNT(*) DESC LIMIT 1

    From the top of my head. Where number is the field you are calculating the modal value for.

    For median:

    SELECT COUNT(*) AS count FROM table;

    Then:
    PHP Code:
    $middle floor($count /2
    Then:

    SELECT number FROM table ORDER BY number DESC LIMIT $middle, 1

    It's late and those are off the top of my head without testing, but should work.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  4. #4
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your median method is pretty much the same as mine but it won't work if there are an even number of elements.

  5. #5
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT number FROM table ORDER BY number DESC LIMIT $middle, 2
    PHP Code:
    $median = ($count 2) ? ($row1 $row2)/$row1
    Obviously you'd need to fetch the two rows and set the number field into $row1 and $row2
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.


Bookmarks

Posting Permissions

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