groupByRaw causing error "isn't in GROUP BY"

so i change a query from this

SELECT 
	complaint_date,
    COUNT(CASE WHEN violation_id = 1 THEN 1 ELSE NULL END) as violation1,
    COUNT(CASE WHEN violation_id = 2 THEN 1 ELSE NULL END) as violation2,
    COUNT(CASE WHEN violation_id = 3 THEN 1 ELSE NULL END) as violation3,
	COUNT(CASE WHEN violation_id = 4 THEN 1 ELSE NULL END) as violation4,
	COUNT(CASE WHEN violation_id = 5 THEN 1 ELSE NULL END) as violation5,
	COUNT(CASE WHEN violation_id = 6 THEN 1 ELSE NULL END) as violation6
FROM 
	complaint
WHERE
	YEAR(complaint_date) = '2023'
GROUP BY 
	MONTH(complaint_date)

to this

$report = Complaint::select(
            DB::raw('@rownum  := @rownum  + 1 AS rownum'),
            'complaint_date',
            DB::raw('COUNT(CASE WHEN violation_id = 1 THEN 1 ELSE NULL END) as violation1'),
            DB::raw('COUNT(CASE WHEN violation_id = 2 THEN 1 ELSE NULL END) as violation2'),
            DB::raw('COUNT(CASE WHEN violation_id = 3 THEN 1 ELSE NULL END) as violation3'),
            DB::raw('COUNT(CASE WHEN violation_id = 4 THEN 1 ELSE NULL END) as violation4'),
            DB::raw('COUNT(CASE WHEN violation_id = 5 THEN 1 ELSE NULL END) as violation5'),
            DB::raw('COUNT(CASE WHEN violation_id = 6 THEN 1 ELSE NULL END) as violation6'),
        )
        ->whereYear('complaint_date', 2023)
        ->groupByRaw('MONTH(complaint_date)')
        ->get();

its nothing wrong when i tested out the sql in query builder but the eloquent got error

Syntax error or access violation: 1055 'wbs.complaint.complaint_date' isn't in GROUP BY

its solve when i just change

->groupByRaw('MONTH(complaint_date)')
into
->groupBy('complaint_date')

but the groupBy did not show as i expected, how do i solve it?

shaungbhone

3 years ago

Level 28

Try this.

->groupBy(\DB::raw('MONTH(complaint_date)'))

user154

3 years ago

Level 1

@shaungbhone nothing change still get the same error

leyduana

3 years ago

Best Answer

Level 8

You need to select your group by column also:

SELECT 
	 MONTH(complaint_date) as month_complaint,
    COUNT(CASE WHEN violation_id = 1 THEN 1 ELSE NULL END) as violation1,
    COUNT(CASE WHEN violation_id = 2 THEN 1 ELSE NULL END) as violation2,
    COUNT(CASE WHEN violation_id = 3 THEN 1 ELSE NULL END) as violation3,
	COUNT(CASE WHEN violation_id = 4 THEN 1 ELSE NULL END) as violation4,
	COUNT(CASE WHEN violation_id = 5 THEN 1 ELSE NULL END) as violation5,
	COUNT(CASE WHEN violation_id = 6 THEN 1 ELSE NULL END) as violation6
FROM 
	complaint
WHERE
	YEAR(complaint_date) = '2023'
GROUP BY 
	month_complaint;

Eloquent:

Complaint::select([
            DB::raw('MONTH(complaint_date) as month_complaint'),
            DB::raw('COUNT(CASE WHEN violation_id = 1 THEN 1 ELSE NULL END) as violation1'),
            DB::raw('COUNT(CASE WHEN violation_id = 2 THEN 1 ELSE NULL END) as violation2'),
            DB::raw('COUNT(CASE WHEN violation_id = 3 THEN 1 ELSE NULL END) as violation3'),
            DB::raw('COUNT(CASE WHEN violation_id = 4 THEN 1 ELSE NULL END) as violation4'),
            DB::raw('COUNT(CASE WHEN violation_id = 5 THEN 1 ELSE NULL END) as violation5'),
            DB::raw('COUNT(CASE WHEN violation_id = 6 THEN 1 ELSE NULL END) as violation6'),
        ])
            ->whereYear('complaint_date', 2023)
            ->groupBy('month_complaint')
            ->get();

Please or to participate in this conversation.