Level 104
Do your grouping in the query rather than in PHP:
DB::table('projects')
->selectRaw('id, DATE_FORMAT(created_at, "%Y%M%d%H") as hour, count(id) as number')
->groupBy('hour')
->get();
1 like
Apr 12, 2018
5
Level 1
Group aggregate query by hour/day etc
I want to count for example number of created projects for every hour. Is this possible to do with a db query. It's easily done in a loop after a query like
$res = DB::table('projects')
->select('id', 'created_at')
->get()->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('Y-m-d H');
});
foreach ($res as $index => $row) {
$res[$index] = $row->count();
}
but that is not as clean and fast as a query would be. The result should be something like
[
"2017-11-20 08" => 2
"2017-11-20 09" => 2
"2017-11-20 11" => 1
"2017-11-20 12" => 5
"2017-11-20 13" => 3
"2017-11-20 19" => 2
]
Thanks!
Level 1
Thank you @tykus, I get
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'foo.projects.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select id, DATE_FORMAT(created_at, "%Y%M%d%H") as hour, count(id) as number from `projects` group by `hour`)
when trying your code.
Level 104
The consequence of ONLY_FULL_GROUP_BY option being enabled. Add id (the first of SELECT to the group by clause:
//...
->groupBy(['id', 'hour'])
EDIT: this is incorrect... see correction below
1 like
Level 1
I did that as well @tykus but the result is as follows:
478 => {#1026 ▼
+"id": 482
+"hour": "2018041211"
+"number": 1
}
479 => {#1027 ▼
+"id": 483
+"hour": "2018041221"
+"number": 1
}
480 => {#1028 ▼
+"id": 484
+"hour": "2018041221"
+"number": 1
}
]
As you can see 479 and 480 should be grouped in the same object with number=2.
Level 104
Sorry... :facepalm: Of course you don't need the id at all... remove that from the SELECT and GROUP BY clauses:
DB::table('projects')
->selectRaw('DATE_FORMAT(created_at, "%Y%m%d%H") as hour, count(id) as number')
->groupBy('hour')
->get();
1 like
Please or to participate in this conversation.