write query without stored procedure and cursor
Posted on 2010-01-11
in my project i am storing users course activity which user readed which lesson and how much time.
you see user_id id 1 studied course_id 1 lessons.
he studied lesson_id 1 10+5=15 second, lesson_id 2 5+2=7 second, lesson_id 3 8 second and lesson_id 4 20 second.
i have other table name is lessons
you see course_id 1 have 6 lessons but user only entered 4 lessons. i am assuming that if user stayed total 10 and more seconds in a lesson he is finished this lesson. when he will enter all 6 lessons and stay total 10 seconds in each lesson he will complete course.
now i want to write a query to report how many users complete this course.
select count((select when sum(d.second) > 10 then 1 else 0 end from studied d where d.lesson_id=l.lesson_id ) ) from lessons l where course_id='1'
but i know this query is wrong and bad query. but i cant write this query about two days.