Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Count data from 2 different table

i have 2 different table "anakelangs" and "absen" I want to retrieve data from both tables and show them in somekind of list result table

Condition here is : get name (there are other columns in this table) from anakelangs table and get the number of total "kehadiran" from "kehadiran" table. Show them in Summary table.

table anakelangs

id nama
1 abiyyu
2 ziggy

table absen

id_anak kehadiran(enum)
1 hadir
2 hadir
1 tidak hadir
2 hadir
1 hadir
2 hadir

table list result

nama kehadiran
abiyyu hadir = 2, tidak hadir = 1
ziggy3 hadir = 3, tidak hadir = 0

i has try something like this in controller

    $rekapabsen = DB::table('anakelangs')
                    ->join('absen', 'anakelangs.id', '=', 'absen.id_anak')
                    ->select('anakelangs.id as id', 'anakelangs.nama_anak as name',
                      DB::raw("count(absen.id_anak) as count")) 
                    ->groupBy('anakelangs.id')
                    ->get();

but its end with result:

SQLSTATE[42000]: Syntax error or access violation: 1055 ‘cakarelang.anakelangs.nama_anak’ isn’t in GROUP BY

Any help will be highly appreciated. Thanks for your time.

Tags:

3

Answers


  1. 0

    All non-aggregated columns have to be part of the GROUP BY clause, which means that you have to add anakelangs.nama_anak into it.

    Login or Signup to reply.
  2. 0

    The error you’re having usually occurs because of MySQL strict mode when using GROUP BY.
    So you’ll need to adjust your query to include all non-aggregated columns from the SELECT list in the GROUP BY clause

    Something like this :

    $rekapabsen = DB::table('anakelangs')
    ->join('absen', 'anakelangs.id', '=', 'absen.id_anak')
    ->select('anakelangs.id as id', 'anakelangs.nama as name', 'absen.kehadiran as status')
    ->groupBy('anakelangs.id', 'anakelangs.nama', 'absen.kehadiran')
    ->get();

    Also advise : keep your tables in English as a best practice

    Login or Signup to reply.
  3. 0 
    $rekapabsen = DB::table('anakelangs')
                ->leftJoin('absen', 'anakelangs.id', '=', 'absen.id_anak')
                ->select(
                    'anakelangs.nama as name',
                    DB::raw("SUM(CASE WHEN absen.kehadiran = 'hadir' THEN 1 ELSE 0 END) as hadir"),
                    DB::raw("SUM(CASE WHEN absen.kehadiran = 'tidak hadir' THEN 1 ELSE 0 END) as tidak_hadir")
                )
                ->groupBy('anakelangs.nama')
                ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search