How to gruopBy hour

How to group by the hour using elocuent? My example:

Item::selectRaw("created_at, COUNT(*) as total, DATE_FORMAT(created_at, '%H:00') as hour") ->groupByRaw('hour') ->pluck('total', 'date');

return ['created' => $itemStatistic];

returned: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression

thank.

MostafaGamal

5 years ago

Level 10

You can't use alias in groupBy in Oracle and SQL Server, not sure whether MySql allows it

try this:

DB::table('items')
	->select(DB::raw('count * as total, DATE_FORMAT(created_at, '%H:00') as hour'))
	->groupBy(DB::raw(' DATE_FORMAT(created_at, '%H:00') '))
	->get();

BRVK

5 years ago

Level 2

Everything ok try this way it will help. Get the result first and use groupBy any column which you mentioned in select()

DB::table('items') ->select(DB::raw('count * as total, DATE_FORMAT(created_at, '%H:00') as hour')) ->get() ->groupBy('hour);

Please or to participate in this conversation.