Cheeku Jee Cheeku Jee - 1 month ago 7
MySQL Question

MySQL minimum records to add up to 10000

I have a MySQL table with two columns:

takenOn
(datetime), and
count
(int).
count
contains the number of steps I have taken.

I'm trying to write a query that will tell me the time when I meet my goal of 10,000 steps every day.

So far, I have the following query:

SET @runningTotal=0;
SELECT
`Date`,
DATE_FORMAT(MIN(takenOn), '%l:%i %p') AS `Time`,
TotalCount
FROM
(SELECT
DATE(s.takenOn) AS `Date`,
s.takenOn,
s.`count`,
@runningTotal := @runningTotal + s.`count` AS TotalCount
FROM
(select * from step where DATE(takenOn) = '2016-10-29') s) temp
WHERE TotalCount >= 10000;


This works, but of course gives me the
MIN(takenOn)
for October 29th only. How can I expand this query to give me
MIN(takenOn)
for all possible dates in the table?

Thank you!

Answer

I am assuming that the steps you care about are all within one day. You are on the right track. Here is the code for multiple days:

SELECT `Date`, DATE_FORMAT(MIN(takenOn), '%l:%i %p') AS `Time`,
       TotalCount
FROM (SELECT DATE(s.takenOn) AS `Date`,
             s.takenOn,
             s.`count`,
             (@runningTotal := if(@d = DATE(s.takenOn), @runningTotal + s.`count`,
                                  if(@d := DATE(s.takeOn), s.`count`, s.`count`)
                                  )
             ) AS TotalCount
      FROM step s CROSS JOIN
           (SELECT @runningTotal := 0, @d = '') params
      ORDER BY takenOn
     ) s
WHERE TotalCount >= 10000;

Note that all the variable assignments are in one expression. This is important because MySQL does not guarantee the order of evaluation of expressions in a SELECT. So, if you split the assignments across more than one expression, you are not guaranteed that the code will work.