Laravel count multiple columns with group by

Hi,

I'm storing student's attendance. It stores the in the present column true or false along with the day column.

I'm fetching the attendance of the current month. I want to show countof total present students and total absent students each day.

So far I managed to get the absent students but how can I get present student at the same time ?

Here is my query to get absent students count for each day of the selected month.

`Table structure`

id student_id -> Student's ID which student was present or absent day -> Attendance day present -> false == absent, true == present

$ViewAttendance = DB::table("students_attendances")->whereRaw('extract(month from day) = ?', [$GetMonth])->whereIn("student_id",$FetchStudents)->where("present",false)->select("day",DB::raw('count(*) as absent'))->groupBy("day")->get();

Here I'm grouping data by day column because 1 day can have multiple records of present and absent.

abhishek009

8 years ago

Level 3

Currently it returns absent student like this.

[
  {
    "day": "2017-10-16 00:00:00",
    "absent": "3"
  },
  {
    "day": "2017-10-17 00:00:00",
    "absent": "5"
  }
]

I would like to show like this.

[
  {
    "day": "2017-10-16 00:00:00",
    "absent": "3",
    "present":"29"
  },
  {
    "day": "2017-10-17 00:00:00",
    "absent": "5"
    "present":"23"
  }
]

abhishek009

8 years ago

Best Answer

Level 3

I have done it using IF statement inside count. If anybody need in case,

Define what you want inside the count statement.

This will count present as well as absent students since I have provided 2 counts with IF condition.

$ViewAttendance = DB::table("students_attendances")->whereRaw('extract(month from day) = ?', [$GetMonth])->whereIn("student_id",$FetchStudents)->select("day",DB::raw('count(IF(present = 1, 1, NULL)) as present'),DB::raw('count(IF(present = 0, 1, NULL)) as absent'))->groupBy("day")->get();

1 like

Please or to participate in this conversation.