Error 1140 using the query builder of Laravel and count aggregate

I want to do the following SQL query using the Laravel query builder:

select `source`, `customer_id`, COUNT(id) from `requests` where `source` = "ATC" and `customer_id` = 1234567

I tried with the following code but it doesn’t work correctly:

DB::table('requests')->select('source', 'customer_id', DB::raw('COUNT(id)'))->where('source', '=', 'ATC')->where('customer_id', '=', '1234567')->get();

I reiceived the following error:

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'opadidb.requests.source'; this is incompatible with sql_mode=only_full_group_by (SQL: select `source`, `customer_id`, COUNT(id) from `requests` where `source` = ATC and `customer_id` = 1234567)

Can help?

Answer

Aggregate functions like sum,count,min,max,avg when selected with other columns it must have a groupBy clause for one of these column.

your query have two columns 'source' & 'customer_id' you must add a group by to both of them:

DB::table('requests')->select('source', 'customer_id', DB::raw('COUNT(id)'))
->where('source', '=', 'ATC')->where('customer_id', '=', '1234567')
->groupBy('source','customer_id')->get();