0

It's a mysql question.

The table has number and frequency columns as following. Frequency is the times that the number appear.

So the numbers are 0,0,0,0,0,0,0,1,2,2,2,3. (there are seven number 0, one number 1, two number 3, three number 4 in the table) 

table: Number
Number   frequency
----------
 0     7
 1     1
 2     3
 3     1

Question： Now I want to return the median of all numbers as following result:

We can know the median is (0+0)/2=0, because the 6th and 7th numbers are 0.

median
  0

How should I write the my sql query?

chloeycs
  • 1
  • 1
  • Your question is not crystal clear. Could you detail a bit more what you're trying to achieve here? – Lucas Jun 30 '19 at 09:21
  • please post clear question so we can help you for get your expected answer. – Bhavesh Tailor Jun 30 '19 at 09:51
  • @Lucas I have edited the question and get it clear. any idea about it? – chloeycs Jun 30 '19 at 22:22
  • @BhaveshTailor I have edited the question and get it clear. any idea about it? – chloeycs Jun 30 '19 at 22:22
  • @chloeycs try this hope it's work SELECT AVG(dd.val) as median_val FROM ( SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum FROM median d, (SELECT @rownum:=0) r WHERE d.val is NOT NULL -- put some where clause here ORDER BY d.val ) as dd WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) ); – Bhavesh Tailor Jul 01 '19 at 05:04

0 Answers0