Statistics - Daily Mode from MySQL table with numbers and date

Question

I need some help writing a MySQL query that grabs the mode (most frequently occurring number) each day

Data set looks something like this

datetime number
2010-01-01 00:35:00 500
2010-01-01 01:15:10 500
2010-01-02 01:25:10 1500
2010-01-02 01:35:10 50
2010-01-03 12:35:50 100
2010-01-05 05:25:10 2500

(etc)

Answer 1

You can use GROUP BY and LIMIT to accomplish this for a given day:

SELECT number, count(number) AS qty 
FROM table 
GROUP BY number 
ORDER BY qty DESC 
LIMIT 1;

Answer 2

The following should give you the mode per day:

SELECT x.* FROM 
(
 SELECT datetime, number, COUNT(*) AS Ct
 FROM table
 GROUP BY datetime, number
) x
INNER JOIN 
(
SELECT datetime, MAX(Ct) AS MaxCt
FROM (
 SELECT datetime, number, COUNT(*) AS Ct
 FROM table
 GROUP BY datetime, number
) y
GROUP BY datetime
) z
ON z.MaxCt = x.Ct
AND z.datetime = x.datetime

n.b. it doesn't do anything fancy where you have multiple numbers with the same modal frequency -- you'll get multiple rows for that date.