-1

How do you get the median of a row in MySQL?

I have a table which gives monthly stock for a series of categories:

cat_id | mar_stk | feb_stk | jan_stk
1      | 5       | 7       | 9
2      | 2       | 1       | 3
3      | 6       | 8       | 10

I need the median, maximum and minimum stock for each category.

Currently have minimum and maximum using:

SELECT
    cat_id,
    GREATEST(mar_stk, feb_stk, jan_stk) AS max_stk,
    LEAST(mar_stk, feb_stk, jan_stk) AS min_stk

FROM example_table

Which leaves me with:

cat_id | max_stk | min_stk
1      | 9       | 5
2      | 3       | 1
3      | 10      | 6

But I can't find any straightforward way to find the median.

Sean M
  • 1
  • 2
  • 2
    It would be best if you normalized your schema, with separate rows for each month. Then you could use a solution like http://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql/7263925#7263925 – Barmar Nov 20 '14 at 10:57
  • check this thread as well : http://stackoverflow.com/questions/17447917/calculate-medians-for-multiple-columns-in-the-same-table-in-one-query-call – NoobEditor Nov 20 '14 at 11:58
  • 1
    To be perfectly honest, I'm not sure how I'd go about changing to have rows for each month. I'm working with a database created by somebody else, which they need in the format it's already in for business reasons, so any solution I come up with will have to start with the table as is. – Sean M Nov 21 '14 at 14:35

1 Answers1

-2

By statistics, Median is the middle number in a given out distribution. For instance if in the column cat_id where you have value 1,2,3 etc. Your median is 2 since its the number or value at the middle. Query the middle value and then hurray. Give me a shout if you still need further guide. ..Sectona

Sectona
  • 98
  • 1
  • 9
  • Thanks, but that would be the median of the column cat_id. I need the median value of each row, excluding cat_id. It also needs to be done automatically, rather than me manually picking out the middle value, since the actual table I have to work with has 12 months worth of data for ~180 categories and will change over time. – Sean M Nov 20 '14 at 11:52
  • how many columns and rows does that table comprises. Secondly do you want it on mysql queries or in php – Sectona Nov 20 '14 at 12:16