Matt - 1 year ago 208
MySQL Question

# Group DB results by Date in Laravel

i have a table that contains the following data :

+--------|-----------|-------------+
|  id    |  date     |   total     |
|--------|-----------|-------------|
|   1    | 2016-01-01|    10       |
|--------|-----------|-------------|
|   2    | 2016-01-01|    20       |
|--------|-----------|-------------|
|   3    | 2016-01-02|    25       |
+----------------------------------+


I am trying to get the following results:

Date : 2016-01-01 | Total : 30
Date : 2016-01-02 | Total : 25


And if another date gets added it will display aswell.
It must just provide the date and the total sum for that date.

This is what i have tried and it prints out every date along with each total , instead of 1 date with the main total of all for that day.

    $data = DB::table('records') ->select(DB::raw('sum(total) as total'), 'id', 'date') ->where('date', '>=', \Carbon\Carbon::now()->startOfMonth()) ->groupBy('date') ->orderBy('id', 'ASC') ->get();  THEN foreach($data as $row){ echo$row->date . "<br>";
echo $row->total; };  Answer Source Because the date is actually datetime, I've made a new query for this which will group by date (Down to the day) and get total in a single query. It's not very Eloquent, but after some research I've figured this is probably the easiest way to do it in a single go. $data = DB::table('records')
->select(DB::raw('date, SUM(total) as total'))
->groupBy(DB::raw('DATE(date)'))
->get();

return var_dump(\$data);