How do i group by department and count the number of days present monthly?

Hello!

Could you please try with that:

SELECT department, DATE_FORMAT(date, "%Y-%b") as `month`, name, SUM(present) AS days_present 
FROM attendance
GROUP BY department, MONTH(date);

Greetings!

Please can you help format this better. Thanks

You want to format better the output or the query? If you want the output, tell me exactly what you need.

the query please. I think i can @foreach the output

Just include the columns you are interested in.

DB::table('attendance')
            ->select('department', DB::raw('DATE_FORMAT(date, "%Y-%b") as `month`'), 'name', DB::raw('SUM(present) AS days_present'))
            ->groupBy('department', DB::raw('MONTH(date)'))
            ->get();

I am getting this

Illuminate\Database\QueryException
SQLSTATE[HY000]: General error: 1 no such function: DATE_FORMAT (SQL: select "department", DATE_FORMAT(date, "%Y-%b") as `month`, "name", SUM(present) AS days_present from "attendance" group by "department", MONTH(date))

Which version of mysql are you using?

i'm using sqlite

done, do i need to switch to mysql?

I have this

$attendance = DB::table('attendance')
        ->select('department', DB::raw('DATE_FORMAT($date, "%Y-%b") as `month`'), 'name', DB::raw('SUM(present) AS days_present'))
        ->groupBy('department', DB::raw('MONTH($date)'))
        ->get();

@tomasosho try to find some of this stuff, like: https://stackoverflow.com/questions/12406295/how-to-query-in-sqlite-for-different-date-format

Does date hold an actual date, like 2020-03-07.

I switched to Mysql I'm getting this

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1055 'sevico.attendance.date' isn't in GROUP BY (SQL: select `department`, DATE_FORMAT(date, "%Y-%b") as `month`, `name`, SUM(present) AS days_present from `attendance` group by `department`, MONTH(date))

My Controller

$date = DB::table('attendance')->select('date')->get();
        //dd($date);
        $attendance = DB::table('attendance')
        ->select('department', DB::raw('DATE_FORMAT(date, "%Y-%b") as `month`'), 'name', DB::raw('SUM(present) AS days_present'))
        ->groupBy('department', DB::raw('MONTH(date)'))
        ->get();

Like @jlrdw said you must remove $date and replace it with date. You've changed the original query that I've sent.

Please check out my updated reply.

I switched to Mysql I'm getting this

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1055 'sevico.attendance.date' isn't in GROUP BY (SQL: select `department`, DATE_FORMAT(date, "%Y-%b") as `month`, `name`, SUM(present) AS days_present from `attendance` group by `department`, MONTH(date))

My Controller

$date = DB::table('attendance')->select('date')->get();
        //dd($date);
        $attendance = DB::table('attendance')
        ->select('department', DB::raw('DATE_FORMAT(date, "%Y-%b") as `month`'), 'name', DB::raw('SUM(present) AS days_present'))
        ->groupBy('department', DB::raw('MONTH(date)'))
        ->get();

You need to turn off full group by for the one query only.

I did a guide https://laracasts.com/discuss/channels/guides/turn-off-only-full-group-by-for-just-one-query-guide

And sorry I missed that date was your actual field name as well.

config()->set('database.connections.your_connection.strict', false);

'your_connection' what does it mean?

In your case it would be, mysql I have different connections with different names.

You can see the documentation that talks about database connections.

Updated not default see top sentence. Sorry I am on mobile here not my desktop.

https://github.com/laravel/laravel/blob/master/config/database.php

Above edited also I'm just wondering if using the keyword date is causing a problem.

Try using ticks around date,

`date`

config()->set('database.connections.default.strict', false);

Try

config()->set('database.connections.default.strict', false);

and try

config()->set('database.connections.mysql.strict', false);

I've only done this with other named connections.

Edit: I tested with mysql, works:

config()->set('database.connections.mysql.strict', false);

That should be what the connection is named.

am i meant to use it in my controller?

config()->set('database.connections.mysql.strict', false);

        $attendance = DB::table('attendance')
        ->select('department', DB::raw('DATE_FORMAT(`date`, "%Y-%b") as `month`'), 'name', DB::raw('SUM(present) AS days_present'))
        ->groupBy('department', DB::raw('MONTH(`date`)'))
        ->get();

Wherever your query is if it's in controller yes or if it's in model then yes for model.

Thanks, It's working... But i want a count for the days present not a sum

Change the aggreate to COUNT.

Illuminate\Support\Collection {#444 ▼
  #items: array:1 [▼
    0 => {#445 ▼
      +"department": "Melanine"
      +"month": null
      +"name": "Victor Okon"
      +"days_present": 1
    }
  ]
}
```
The month is null

My Month is null, it's not picking it from my date.

        $date = DB::table('attendance')->select('date')->get();
        $attendance = DB::table('attendance')
        ->select('department', DB::raw('DATE_FORMAT(`date`, "%Y-%b") as `month`'), 'name', DB::raw('count(present) AS days_present'))
        ->groupBy('department', DB::raw('MONTH(`date`)'))
        ->get();