Custom MODE() function usable like MIN() or AVG() in MySQL

Question

Just like MIN() and AVG() i sometimes need the most common occuring value (AKA the mode) of an integer value. This can be obtained as such:

SELECT column, COUNT(*) AS x 
FROM table 
GROUP BY column 
ORDER BY x DESC
LIMIT 1

And thats quite a mouthful. I'd really like to use it like: MODE()

SELECT AVG(`sTemperature`), MODE(`sSwitch`), MODE(`sDoor`)
FROM `stats`;

Is there a way to make the above query work for Integers? For Booleans, i figure ROUND(AVG()) is fine.

Its possible to define functions in a MySQL DB, but can they work on datasets as MIN() or AVG() does?

Because i use it a lot inside things like:

INSERT INTO `statsaggregates` (
  `saMeasurements`, `saTemperature`, `saSwitch`, `saDoor`
) SELECT 
  COUNT(*)            as 'saMeasurements',
  AVG(`sTemperature`) as 'saTemperatureAvg',
  MIN(`sTemperature`) as 'saTemperatureMin',
  MAX(`sTemperature`) as 'saTemperatureMax',
  MODE(`sSwitch`)     as 'saSwitch',
  MODE(`sDoor`)       as 'saDoor'
FROM `stats`
WHERE 1

Note: There are other SO questions like Select most common value from a field in MySQL that ask how to calculate mode. This question is about using it exactly like AVG() in a function.

score 3 · Accepted Answer · answered Feb 21 '14 at 09:18

No, unfortunately, you can not do that with MySQL UDF "normal" definition possibility. The only way to do this is to define such thing in C/C++ but not SQL:

For the UDF mechanism to work, functions must be written in C or C++ and your operating system must support dynamic loading. MySQL source distributions include a file sql/udf_example.c that defines five UDF functions. Consult this file to see how UDF calling conventions work. The include/mysql_com.h header file defines UDF-related symbols and data structures, although you need not include this header file directly; it is included by mysql.h.

Custom MODE() function usable like MIN() or AVG() in MySQL

1 Answers1