# Simple way to calculate median with MySQL

What's the simplest (and hopefully not too slow) way to calculate the median with MySQL? I've used `AVG(x)` for finding the mean, but I'm having a hard time finding a simple way of calculating the median. For now, I'm returning all the rows to PHP, doing a sort, and then picking the middle row, but surely there must be some simple way of doing it in a single MySQL query.

Example data:

``````id | val
--------
1    4
2    7
3    2
4    2
5    9
6    8
7    3
``````

Sorting on `val` gives `2 2 3 4 7 8 9`, so the median should be `4`, versus `SELECT AVG(val)` which == `5`.

45

``````SELECT AVG(dd.val) as median_val
FROM (
SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
FROM data 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) );
``````

Steve Cohen points out, that after the first pass, @rownum will contain the total number of rows. This can be used to determine the median, so no second pass or join is needed.

Also `AVG(dd.val)` and `dd.row_number IN(...)` is used to correctly produce a median when there are an even number of records. Reasoning:

``````SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3
``````

Finally, MariaDB 10.3.3+ contains a MEDIAN function

Tuesday, June 1, 2021

74

From the PHP Manual on `mysqli_stmt::execute`:

`mysqli_stmt::execute` -- `mysqli_stmt_execute`Executes a prepared Query

Returns TRUE on success or FALSE on failure.

``````if (\$stmt->execute()) { // exactly like this!
\$success = true;
}
``````

You're doing it right... What's your dilemma?

Wednesday, March 31, 2021

22

Cross-tabs or pivot tables is the answer. From there you can SELECT FROM ... INSERT INTO ... or create a VIEW from the single SELECT.

Something like:

``````SELECT country,
MAX( IF( key='President', value, NULL ) ) AS President,
MAX( IF( key='Currency', value, NULL ) ) AS Currency,
...

FROM table
GROUP BY country;
``````

Tuesday, June 29, 2021

39

Longer answer, you can use a variable to tally it up as it iterates down the rows, i.e.

``````SELECT
`table`.`ID`,
`table`.`In`,
`table`.`Out`,
@Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`
FROM `table`, (SELECT @Balance := 0) AS variableInit
ORDER BY `table`.`ID` ASC
``````

The `, (SELECT @Balance := 0) AS variableInit` ensures that @Balance is initialised to 0 before you start. For each row it then sets @Balance to be `@Balance + In - Out`, and then outputs the calculated value.

Also it's worth making certain the ORDER is consistent as otherwise the Balance will vary depending on what order the rows are returned. If you wanted to then order it back to front, for example, you could use this as a subquery as then the outer query deals with the calculated values thus ensuring the Balance remains correct i.e.

``````SELECT
`balanceCalculation`.`ID`,
`balanceCalculation`.`In`,
`balanceCalculation`.`Out`,
`balanceCalculation`.`Balance`
FROM (
SELECT
`table`.`ID`,
`table`.`In`,
`table`.`Out`,
@Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`
FROM `table`, (SELECT @Balance := 0) AS variableInit
ORDER BY `table`.`ID` ASC
) AS `balanceCalculation`
ORDER BY `balanceCalculation`.`ID` DESC
``````
Monday, August 2, 2021

68
``````int even = (0 == begin % 2) ? (end - begin) / 2 + 1 : (end - begin + 1) / 2;
``````

Which can be converted into:

``````int even = (end - begin + (begin % 2)) / 2 + (1 - (begin % 2));
``````

EDIT: This can further simplified into:

``````int even = (end - begin + 2 - (begin % 2)) / 2;
``````

EDIT2: Because of the in my opinion somewhat incorrect definition of integer division in C (integer division truncates downwards for positive numbers and upwards for negative numbers) this formula won't work when begin is a negative odd number.

EDIT3: User 'iPhone beginner' correctly observes that if `begin % 2` is replaced with `begin & 1` this will work correctly for all ranges.

Monday, September 27, 2021