shalini shalini - 3 months ago 7
MySQL Question

Get most viewed url by users in a day

I have a table whose structure like this

id time userid ip course module cmid action url info
1 1441006367 2 110.142.152.217 1 user 0 update view.php?id=2&course=1
2 1441006397 2 110.142.152.217 1 course 0 view view.php?id=1 1
3 1441061491 2 110.142.152.217 1 user 0 view view.php?id=0&course=1 2
4 1441061491 2 110.142.152.217 1 course 0 view view.php?id=1 1
5 1441067607 2 110.142.152.217 1 course 0 view view.php?id=1 1
6 1441067617 2 110.142.152.217 1 course 0 view view.php?id=1 1
7 1441067646 2 110.142.152.217 1 course 0 view view.php?id=1 1
8 1441067681 2 110.142.152.217 1 course 0 view view.php?id=1 1
9 1441067774 2 110.142.152.217 1 course 0 view view.php?id=1 1
10 1441069218 2 110.142.152.217 1 course 0 view view.php?id=1 1
11 1441071815 2 110.142.152.217 1 course 0 view view.php?id=1 1
12 1441071815 2 110.142.152.217 1 course 0 login view.php?id=1 1
13 1441080275 2 110.142.152.217 1 user 0 view view.php?id=0&course=1 2
14 1441080275 2 110.142.152.217 1 course 0 view view.php?id=1 1
15 1441080275 2 110.142.152.217 1 course 0 view view.php?id=1 1
16 1441082380 2 110.142.152.217 1 course 0 view view.php?id=1 1
17 1441082494 2 110.142.152.217 1 course 0 view view.php?id=1 1
18 1441082498 2 110.142.152.217 1 user 0 logout view.php?id=2&course=1 2
19 1441082504 2 110.142.152.217 1 user 0 login view.php?id=0&course=1 2
20 1441082505 2 110.142.152.217 1 user 0 login view.php?id=0&course=1 2
21 1441082508 2 110.142.152.217 1 user 0 login view.php?id=0&course=2 2
22 1441082508 2 110.142.152.217 1 user 0 loam view.DhD?id=0&course=1 2


I want to get most viewed url by users in a day where each url belongs to different course. One user can view multiple url but not same course.
I tried this query

SELECT count(DISTINCT `course`),`userid`,`course`,`module`,`url`, FROM_UNIXTIME(`time`,'%Y-%m-%d') as date FROM `mdl_log` where `time`>unix_timestamp('2016-06-01 00:00:00') AND `time`<unix_timestamp('2016-06-02 00:00:00') GROUP BY `course`


When i run this it restrict same course by a user which is right but this restrict also for other user.
For example if one user viewed same course 2 time then it count one(that's right) but it not count for user who viewed 1 time.Means they not getting same course for other user. Please help me what i am doing wrong..

Answer

Because you are counting the column you are grouping by ! that is totally wrong.

You should count DISTINCT USER_ID :

SELECT count(DISTINCT `userid`),`userid`,`course`,`module`,`url`, FROM_UNIXTIME(`time`,'%Y-%m-%d') as date 
FROM `mdl_log` 
where `time`>unix_timestamp('2016-06-01 00:00:00') AND 
      `time`<unix_timestamp('2016-06-02 00:00:00')
GROUP BY `course`

I don't understand why are you even selecting userid ? It will be randomly picked because it's not a part of the group by clause.

Comments