YEARWEEK using group by clause in mysql

I want to get individual users activity in week with single record from one week, let say 2 students activity from week(6 days) and output should be group by week and individual user. but query which I wrote not working as per my expected output:

students table:

id   name

1    student_1

2    student_2

3    students_3

student_activity table

id   student_id      date

1      1        2020-10-05

2      2        2020-10-05
       
3      1        2020-10-07
       
4      2        2020-10-07
       
5      1        2020-10-12 
       
6      2        2020-10-12
       
7      1        2020-10-15
       
8      2        2020-10-15

Expected output:

student_id   week_no
       
    1       202040
       
    2       202040
                
    1       202041
                
    2       202041

Query:

$this->db->select('user_activity.*,user.name,YEARWEEK(user_activity.date) as week_no');
$this->db->from('user_activity');
$this->db->join('students','students.id=student_activity.student_id');
$this->db->group_by('week_no,user_activity');
$this->db->get()->result();


Read more here: https://stackoverflow.com/questions/64402248/yearweek-using-group-by-clause-in-mysql

Content Attribution

This content was originally published by msp at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: