Lookup GROUP BY in the Mysql manual, it explains all of this.
Query not working in laravel query builder
i created a query in query builder app and returning result like i want
SELECT
answers.name,
COUNT(CASE WHEN survey.village_id = 3272010006 THEN 1 ELSE NULL END ) AS result,
FROM
results
LEFT JOIN survey ON results.survey_number = survey.survey_number
LEFT JOIN answers ON results.answer_id = answers.id
WHERE
results.question_id = 1 AND
results.answer_id IS NOT NULL AND
YEAR(survey.survey_date) = 2021
GROUP BY
results.answer_id
but when i convert into laravel query builder it return error violation
$query = DB::table('results')
->select(
'answers.name as answer_name',
DB::raw('COUNT(CASE WHEN survey.village_id = 3272010006 THEN 1 ELSE NULL END ) AS result')
)->leftJoin('survey', 'results.survey_number', '=', 'survey.survey_number')
->leftJoin('answers', 'results.answer_id', '=', 'answers.id')
->where('results.question_id', $question)
->where('results.answer_id', '!=', null)
->whereYear('survey.survey_date', 2021)
->groupBy('results.answer_id')
->get();
return $query;
the error
Syntax error or access violation: 1055 'answers.name' isn't in GROUP BY
it work when i delete the answers.name
Check "select @@sql_mode" (using laravel) If it shows, amgonst other things, ONLY_FULL_GROUP_BY, then that's your problem
PDO will connect to mysql in a more "strict" manner than whatever client you're using to run the query directly
https://stackoverflow.com/questions/6975351/how-can-i-set-the-sql-mode-while-using-pdo
You might get rid of that restriction, but you should realy try to write the query with query builder in a way you don't need "hack" it
Please or to participate in this conversation.