How to select median value?

Question

Could anybody give me a hint how to find median value for "tax" during 12.04.18 - 16.04.18:

user_id  login_time  tax
3       2018-04-15   16625000
5       2018-04-16      
6       2018-04-17   296470000
6       2018-04-16   192519750
6       2018-04-15   4455500
6       2018-04-13   17125
6       2018-04-12   120180000
7       2018-04-18   24060000
7       2018-04-17   42959500

The result equals 16625000 (because there is NULL value. We need to use it as 0).

Thank U for attention to my question!

@SMA: median is not the same as mean (also called average). AVG gives the mean, not the median. — Steffen Ullrich, Sep 09 '18 at 16:04

dognose · Answer 1 · 2018-09-09T17:10:44.650

0

The median is the value

located exactly in the middle of an odd dataset.
Or the average of the two middle values in an even dataset.

So, by considering this two cases, the first you need is the count of datarows. Then, you have to decide (simple case) if you pick the value in the middle, or if you need the average of two values (Don't forget to apply sorting prior to selecting the actual values):

I would use a little "code" to achieve this:

Pseudo-Code:

1.) SELECT count(id) AS val FROM myTable WHERE datetime ... //$val=9
2.) Programming language: $lim = floor($val/2); // $lim=4

if odd($val){
  3.) SELECT tax FROM myTable WHERE datetime [...] ORDER BY tax LIMIT $lim,1 
}

else if even($val){
  3.) Programming language: $lim -=1; // if $val was 10, we want row 4 and 5
  4.) SELECT AVG(tax) AS tax FROM 
       (SELECT * FROM myTable WHERE datetime [...] ORDER BY tax LIMIT $lim,2) AS tmp
}
[...]
echo "Median is: ". $row["tax"];

edited Sep 09 '18 at 17:10

answered Sep 09 '18 at 16:52

dognose

18,985
9
54
99

thanks,is it possibe to solve this task without using $? – Елисей Горьков Sep 09 '18 at 17:48
@ЕлисейГорьков sure, that was just an example of variable declaration. You should use the declaration your programming language supports. – dognose Sep 09 '18 at 18:00
I use your hint but I have difficulties. It's my query: 1) SELECT count(user_id) from activity where login_time between '2018-04-12' and '2018-04-16'; 2) SELECT tax from activity where login_time between '2018-04-12' and '2018-04-16' order by tax; 3) SELECT AVG(tax) as tax from (select * from activity where login_time between '2018-04-12' and '2018-04-16' order by tax) d; What is wrong here? – Елисей Горьков Sep 09 '18 at 18:45
@ЕлисейГорьков hard to say, Can you update your initial post with the results you are getting from each query? Then we might be able to figure out whats going wrong. The `LIMIT` part is important here, as it determines which values to fetch from the database! – dognose Sep 09 '18 at 18:59

How to select median value?

1 Answers1