1
 set @ct :=(select count(*) from medi);
 set @ro :=0;
 select  avg(num) as median from (select * from medi order by num)  
 where (select @ro:= @ro+1)      
 between @ct/2.0 AND @ct/2.0+1;

Values in table
+------+
| num |
+------+
| 2 |
| 55 |
| 63 |
| 85 |
| 32 |
| 15 |
| 3 |
| 36 |
| 69 |
+------+
Need help to understand the sequence of execution and the The role of @ro in where clause

Ravdeep Singh
  • 37
  • 5

1 Answers1

0

Well to begin with this query produces the incorrect result. It produced 32 the median, which is incorrect. The median should be 36

The median is the value separating the higher half of a data sample, a population, or a probability distribution, from the lower half. In simple terms, it may be thought of as the "middle" value of a data set. For example, in the data set {1, 3, 3, 5, 9, 11, 13}, the median is 5, the fourth number in the sample. The median is a commonly used measure of the properties of a data set in statistics and probability theory.

Ref: https://en.wikipedia.org/wiki/Median

The problem with this bit of SQL is that it does not order the values which is required for determining the median.

This stackoverflow Q&A: Simple way to calculate median with MySQL has lots of different ways to calculate median and with explanations given.

If you really want to know what the above query does
Step1: Count the number of rows in the table
Step2: Create a variable and asign it to zero
Step3: Use the variable as an number that increments by one for each row to find the halfway mark of the table. Take the average of the numbers that lie next to the halfway mark.

Now you will realize why it's wrong, it does not impose any ordering on the contents of the table.

Community
  • 1
  • 1
e4c5
  • 48,268
  • 10
  • 82
  • 117
  • Thanks a lot , this helped me a lot , i will try to add order by in this query – Ravdeep Singh Jul 21 '16 at 05:10
  • Glad to have helped. Since you are still new here, permit me to point you to this link: http://stackoverflow.com/help/someone-answers – e4c5 Jul 21 '16 at 05:16
  • I think this code is much smaller and efficient than the answers provided in http://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql can you please compare and tell if this is more or less efficient – Ravdeep Singh Jul 21 '16 at 05:23
  • Efficiency depends on whether or not that column has an index on it. If no index is provided, this will result in one table scan while some of the answers in that question will indeed result in two table scans. So this is more efficient provided you can add the sorting. – e4c5 Jul 21 '16 at 05:39