Laravel Error SQLSTATE[42000]: Syntax error or access violation: 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

Possible duplicate of [Laravel : Syntax error or access violation: 1055 Error](https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error) — aynber, Jan 11 '19 at 16:34

score 0 · Answer 1 · answered Jan 11 '19 at 16:37

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")))');

Laravel Error SQLSTATE[42000]: Syntax error or access violation: 1055

1 Answers1