Laravel错误SQLSTATE [42000]：语法错误或访问冲突：1055

Question

why the query syntax in the Laravel application can't be run but it can be in phpmyadmin syntax, namely:

$dataTanggal = DB::select('SELECT CONCAT (YEAR(tanggal_pinjam),"-",LPAD(MONTH(tanggal_pinjam), 2, '0')) as tanggal_pinjam, COUNT(*) As jumlah_data FROM riwayat_pinjam GROUP BY YEAR(tanggal_pinjam),MONTH(tanggal_pinjam)');

and the error in the laravel is :

SQLSTATE[42000]: Syntax error or access violation: 1055 'sipirang.riwayat_pinjam.tanggal_pinjam' isn't in GROUP BY (SQL: SELECT * from lihatperbulan)

can anyone help? please help me

Answer 1

seems you have wrong si gle quote aroud 0, avoid ambiguity between column name tanggal_pinjam and alias tanggal_pinjam (use a different alias eg: my_ tanggal_pinjam), and use the same code for group by

  $dataTanggal = DB::select('SELECT CONCAT (YEAR(tanggal_pinjam),"-"
        , LPAD(MONTH(tanggal_pinjam), 2, "0")) as my_tanggal_pinjam
        , COUNT(*) As jumlah_data 
        FROM `riwayat_pinjam` 
        GROUP BY YEAR(tanggal_pinjam),MONTH(tanggal_pinjamLPAD(MONTH(tanggal_pinjam), 2, "0")))');