?
Solved

write query without stored procedure and cursor

Posted on 2010-01-11
13
Medium Priority
?
453 Views
Last Modified: 2013-12-07
in my project i am storing users course activity which user readed which lesson and how much time.
studied
id,user_id,course_id,lesson_id,second
1,1,1,1,10
2,1,1,2,5
3,1,1,3,8
4,1,1,2,2
5,1,1,1,5
6,1,1,4,20

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
lessons
lesson_id,course_id,title
1,1,title1
2,1,title2
3,1,title3
4,1,title4
5,1,title5
6,1,title6

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.
0
Comment
Question by:phparmy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 26282896

select user_id, sum(second) as totseconds from studied d group by user_id having sum(second) > 10)
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26282914
select count(case when sum(d.second) > 10 then 1 else 0 end) cnt_nbr
from studied d , lessons l
where d.lesson_id=l.lesson_id and  course_id='1'

try this
0
 

Author Comment

by:phparmy
ID: 26283067
rajvja select user_id, sum(second) as totseconds from studied d group by user_id having sum(second) > 10)

you misunderstand.

i dont want totalseconds. there are 6 lessons in course and our table says us that user have finished 2 lessons which lesson_id 1 15 seconds and lesson_id 4 20 seconds. i want to count how many users have finished 6 lessons. means how many user have studied 6 lesson minimum 10 seconds.

shru_0409 this query not works i dont thing it will be such easy query. i think we have to use group by and having but i dont know in which step i have to use.
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26283178
Something like this perhaps:

SELECT  [USER_ID],
        Course_ID
FROM    Studied s
        INNER JOIN Lessons l ON s.lesson_id = l.lesson_id
WHERE   s.course_id = '1'
HAVING  COUNT(*) > 6
        AND SUM([SECOND]) >= 10
0
 
LVL 32

Expert Comment

by:awking00
ID: 26283480
This question is listed in three different zones representing three different dbms. Which dbms and version are you using?
0
 

Author Comment

by:phparmy
ID: 26283544
acperkins

it is good query but it is not looking each lesson distinctly only compute all.

i can explain via php code what i want.

<?php

$m1 = mysql_query("select count(*) as total_lesson from lessons where course_id='1'");
$m2 = mysql_fetch_assoc($m1);
$total_lesson = $m2[total_lesson];
$counter=0;

$s1 = mysql_query("select sum(s.second) as read_second from lessons l left join readed s on (l.course_id=s.course_id and l.lesson_id=s.lesson_id and s.user_id='1') where l.course_id='1' group by l.lesson_id");
while($s2 = mysql_fetch_assoc($s1)){
     if($s2[read_second]>=10){
          $counter++;    
     }
}

if($counter==$total_lesson){
   echo "user_id 1 completed lesson";
}
?>

purpose is that how many users completed course. each lesson in course evaulated indivually you can think it is a cursor in stored procedure.

0
 

Author Comment

by:phparmy
ID: 26283585
awking00

it is not important you can write any of them i can translate query to my dmbs which i need now.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26283686
try this

select t2.user_id
  from (select course_id,count(distinct lesson_id) lesson_id_cnt from lessons group by course_id) as t1
  join (select user_id,course_id,count(distinct lesson_id) lesson_id_cnt from studied group by user_id,course_id) as t2
    on t1.course_id = t2.course_id 
  join (select user_id,course_id,lesson_id,sum(second) as second_sum from studied group by user_id,course_id,lesson_id) as t3 
   on t1.course_id = t3.course_id and t2.user_id = t3.user_id
where t1.lesson_id_cnt = t2.lesson_id_cnt 
  and t3.second_sum > 10

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26283732
The above query will display all the users for all the courses who satisfy your requirement. You can include course_id also in the SELECT clause.

select t2.user_id, t2.course_id
  from (select course_id,count(distinct lesson_id) lesson_id_cnt from lessons group by course_id) as t1
  join (select user_id,course_id,count(distinct lesson_id) lesson_id_cnt from studied group by user_id,course_id) as t2
    on t1.course_id = t2.course_id
  join (select user_id,course_id,lesson_id,sum(second) as second_sum from studied group by user_id,course_id,lesson_id) as t3
   on t1.course_id = t3.course_id and t2.user_id = t3.user_id
where t1.lesson_id_cnt = t2.lesson_id_cnt
  and t3.second_sum > 10

If you wan to run the query for specific course_id = 1 then include additional filter like this.

select t2.user_id
  from (select course_id,count(distinct lesson_id) lesson_id_cnt from lessons group by course_id) as t1
  join (select user_id,course_id,count(distinct lesson_id) lesson_id_cnt from studied group by user_id,course_id) as t2
    on t1.course_id = t2.course_id
  join (select user_id,course_id,lesson_id,sum(second) as second_sum from studied group by user_id,course_id,lesson_id) as t3
   on t1.course_id = t3.course_id and t2.user_id = t3.user_id
where t1.lesson_id_cnt = t2.lesson_id_cnt
  and t3.second_sum > 10
  and t1.course_id = 1
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 26284558
For Oracle, see attached.
query.txt
0
 

Author Comment

by:phparmy
ID: 26292218
Sharath_123

your query first checks if all lesson are readed. after checks which lessons readed more than 10 seconds. but i want to find how many users completed course. means user readed all lessons and he readed each lesson more than 10 seconds.

i am modified and tried your query but no i cant find solution.
0
 
LVL 32

Expert Comment

by:awking00
ID: 26293379
My query determines the lessons finished (i.e. second >= 10) and compares that to the lessons available to determine if the course has been completed. Isn't that what you wanted?
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 26297890
The below query gives you count of all the lesson ids for a course id from lessons table.

select course_id,count(distinct lesson_id) lesson_id_cnt from lessons group by course_id

The below query gives you the count of lesson ids for the combination of user id and course id from studied table.

select user_id,course_id,count(distinct lesson_id) lesson_id_cnt from studied group by user_id,course_id

The below query gives you the sum of seconds for the combination of user id, course id and lesson id from studied table.

select user_id,course_id,lesson_id,sum(second) as second_sum from studied group by user_id,course_id,lesson_id

If the counts from 1st and 2nd query are equal, the user attended all the lessons. For those users, if you check the sum of the seconds for more than 10, you will get the list of users who attended all the lessons more than 10 seconds.

>> i am modified and tried your query but no i cant find solution.

for the sample set provided by you, the user 1 does not satisfy the conditions and the result from my query is blank.

Provide me the sample set where exactly this query is failing.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question