Matt Matt - 5 months ago 112
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

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);