-1

How can I calculate the median of each year for each code. Below is the mysql table:

tbl_sales:

+-----+------+------+------+
| cod | cant | mont | year |
+-----+------+------+------+
| 432 |   1  |   10 | 2012 |
| 432 |   2  |   10 | 2012 |
| 432 |   4  |   10 | 2012 |
| 432 |   2  |   3  | 2012 |
| 432 |   3  |   2  | 2016 |
| 432 |   2  |   1  | 2016 |
| 432 |   8  |   8  | 2017 |
+-----+------+------+------+

Manual calculation of the median:

+------+---+---+---+---+---+---+---+---+---+----+----+----+-------+
| year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | TOTAL |
+------+---+---+---+---+---+---+---+---+---+----+----+----+-------+
| 2012 |   |   | 2 |   |   |   |   |   |   |  7 |    |    |   9   |
| 2016 | 2 | 3 |   |   |   |   |   |   |   |    |    |    |   5   |
| 2017 |   |   |   |   |   |   |   | 8 |   |    |    |    |   8   |
+------+---+---+---+---+---+---+---+---+---+----+----+----+-------+

MEDIAN (5,8,9) = 8

How can I display in sql this way: 432 => 8

Thank you!

Strawberry
  • 32,714
  • 12
  • 37
  • 56
ster
  • 181
  • 1
  • 12
  • Possible duplicate of https://stackoverflow.com/questions/5401158/calculating-the-median-with-mysql – MatSnow Aug 16 '17 at 07:02

1 Answers1

0

The kernel of the query is (obviously?):

SELECT year,SUM(cant) total FROM my_table GROUP BY year ORDER BY year;

From here, I would handle the rest in application code. You can calculate medians in SQL, but not without further hits on the data base, which just seems tedious when you already have all the information needed to perform the calculation (the number of rows and the value of the middle row(s)) right here in this result.

Strawberry
  • 32,714
  • 12
  • 37
  • 56
  • Actually, cod is always the same value, but in the event that it changes, you've not stated how that should be managed. That said, in any event, the fix is trivial. Also, it may benefit you to be a little more polite. – Strawberry Aug 16 '17 at 09:44