# Thread: Mode/Medium Question

1. ## 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. 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. 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.

4. Your median method is pretty much the same as mine but it won't work if there are an even number of elements.

5. SELECT number FROM table ORDER BY number DESC LIMIT \$middle, 2
PHP Code:
``` \$median = (\$count % 2) ? (\$row1 + \$row2)/2 : \$row1;  ```
Obviously you'd need to fetch the two rows and set the number field into \$row1 and \$row2

#### Posting Permissions

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