Pete Naylor Pete Naylor - 6 months ago 17
SQL Question

mysql group by and count attempts on seperate column

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

This produces the below result

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.

Answer

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 ....