I have a mySQL query that groups results by quiz attempt ID:
SELECT * FROM quiz_log WHERE archived = 0 GROUP BY quiz_attempt_id ORDER BY quiz_attempt_id ASC
My question is how do I now count up the attempts in the by app_user_id. The app_user_id 150 appears three times, so I need another coulum with the number 1 on the first line, 2 on the 3rd line and 3 on the 19th line.
You can use a correlated query:
SELECT t.*, (SELECT count(distinct s.quiz_attempt_id) FROM quiz_log s WHERE s.app_user_id = t.app_user_id AND s.timestamp <= t.timestamp) as Your_Cnt FROM quiz_log t WHERE ....