How to fetch data datewise and show in html table in blade view

What goes in the yellow fields? The names of those who attended or a count of how many attended?

And I would suggest learning sql, instead of turning off strict mode :)

Oh and as you arent using eloquent at all, your relationships arent used either

Thanks Sinnbeck, for your prompt reply. The yellow fileds in picture contains number of students who are present in class on a particular day out of total students. And I am doing my best to learn sql as well.

Ok give this a try then

$data = DB::table('attendances')
        ->join('school_classes', 'school_classes.id', '=', 'attendances.school_class_id')
        ->select(DB::raw('count(attendances.*) as attendance_count, school_classes.class_grade, attendances.date'))
        ->groupBy('school_classes.class_grade', 'attendances.date')
        ->get();

I also used the eloquent way but I was unable to get desired results. As far I understand I am missing something about groupBy clause, that is how this works actually.

now I am getting this error

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as attendence_count, school_classes.class_grade, attendances.date from `atten' at line 1 (SQL: select count(attendances.*) as attendence_count, school_classes.class_grade, attendances.date from `attendances` inner join `school_classes` on `school_classes`.`id` = `attendances`.`school_class_id` group by `school_classes`.`class_grade`, `attendances`.`date`)

Try this instead

$data = DB::table('attendances')
        ->join('school_classes', 'school_classes.id', '=', 'attendances.school_class_id')
        ->select(DB::raw('count(attendances.present) as attendance_count, school_classes.class_grade, attendances.date'))
        ->groupBy('school_classes.class_grade', 'attendances.date')
        ->get();

thanx, its working now. Sorry for being naive, but if you could please give some hint about how to loop through this to get results in table format, as mentioned in screenshot earlier.

[
{
"attendance_count": 1,
"class_grade": "1",
"created_at": "2020-08-11 22:57:36"
},
{
"attendance_count": 1,
"class_grade": "2",
"created_at": "2020-08-10 17:31:40"
},
{
"attendance_count": 1,
"class_grade": "2",
"created_at": "2020-08-11 23:14:14"
},
{
"attendance_count": 1,
"class_grade": "2",
"created_at": "2020-08-12 00:02:47"
},
{
"attendance_count": 1,
"class_grade": "3",
"created_at": "2020-08-10 18:02:52"
},
{
"attendance_count": 1,
"class_grade": "3",
"created_at": "2020-08-11 10:39:48"
},
{
"attendance_count": 1,
"class_grade": "4",
"created_at": "2020-08-10 18:05:20"
},
{
"attendance_count": 1,
"class_grade": "4",
"created_at": "2020-08-10 18:05:40"
},

Happy to help :) But isnt there an attendance for each attendance? Or is attendance you a number? My query assumes that there is an attendance row for each person attending. Also consider making a date column for just the date (instead of using created at). The reason for using a seperate column.. Consider that for some reason your system missing creating a record yesterday.. So you just add it today.. But now the created_at is for today and not yesterday.. With a seperate column you can see that you created the row today, but that the data is really for yesterday.

If it just a count, I will give you a better example :)

The attendances table has a row for each class as a whole, like in school_classes table, class_grade=4 has 100 students and there are 80 students present in class on a given day. this is represented by present column in attendences table

And I am also modifying my table to include date columns. Anyway you have pointed me into right direction, Now I can move further. Thanku very much.

Ok. Then dont use count as that will always show 1 (there is just row per grade per date)