Php - Laravel :Sum of rows in group by date

Question

I am having data :

I want to get result for sum of each row by perticular created_at. For example, sum of labour,rent etc. on date of "2020-10-12".

I tried query which gives me data of perticular week :

$cashFlowDetails =  CashModel::whereBetween('created_at', [
            now()->locale('en')->startOfWeek()->subWeek() ,
            now()->locale('en')->endOfWeek()->subWeek() ]) 
             ->get();

You may try something like `CashModel::whereDate('created_at', '2020-10-12')->sum('labour');` — Donkarnash, Nov 25 '20 at 03:09
what if I wants to get sum of all rows? like labour,rent,electricity and so on — Damini Suthar, Nov 25 '20 at 03:12
Can you upload a sql dump of your table with small set of data somewhere from where I can download - then I can try and write the query for your desired output. As such you can do individual query for each of labour, rent, electricity and so on — Donkarnash, Nov 25 '20 at 03:18
@DaminiSuthar make groupby and add a select statement like `->select(\DB::Raw('sum('fieldname') as totalField)` groupby is on that solution — Kamlesh Paul, Nov 25 '20 at 03:41
like this https://stackoverflow.com/questions/64276912/list-of-users-with-the-highest-sum-of-points/64277731#64277731 — Kamlesh Paul, Nov 25 '20 at 03:45

score 1 · Answer 1 · answered Nov 25 '20 at 04:32

As far as I understand you need the date wise totals for each of labour, electricity and so on.

Below query should get you the desired results.

$cashFlowDetails = Cash::groupBy('created_at')
    ->selectRaw(
        'created_at,
         sum(labour) as labour,
         sum(rent) as rent,
         sum(electricity) as electricity,
         sum(creditors) as creditors,
         sum(gst) as gst,
         sum(insurance) as insurance,
         sum(direct_debits) as direct_debits,
         sum(others) as others, 
         sum(total_amount) as total_amount',        
    )
    ->whereBetween('created_at', [
        now()->locale('en')->startOfWeek()->subWeek() ,
        now()->locale('en')->endOfWeek()->subWeek() ])
    ->get();

Mudit Gulgulia · Answer 2 · 2020-11-25T04:25:08.990

You can use a mutator on CashModel and append a new column which will hold a total of every row separately.

Controller:

public function index()
{
    return CashModel::whereBetween('created_at', [
        now()->locale('en')->startOfWeek()->subWeek(),
        now()->locale('en')->endOfWeek()->subWeek()
    ])->get();
}

Model:

public $appends = ['total_sum'];

public function getTotalSumAttribute($value)
{
    return $this->opening_balance +
        $this->labour +
        $this->rent +
        $this->electricity +
        $this->creditors +
        $this->gst +
        $this->insurance +
        $this->direct_debits +
        $this->other +
        $this->total_amount;
}

Output Will be:

[
    {
        "id": 1,
        "labour": 215,
        "rent": 5412,
        "electricity": 1652,
        "creditors": 845,
        "gst": 161,
        "insurance": 332,
        "direct_debits": 1552,
        "other": 2165,
        "total_amount": 464,
        "created_at": "2020-11-15T00:00:00.000000Z",
        "updated_at": "2020-11-25T00:00:00.000000Z",
        "total_sum": 12798
    },
    {
        "id": 6,
        "labour": 771,
        "rent": 2087,
        "electricity": 1517,
        "creditors": 760,
        "gst": 2947,
        "insurance": 103,
        "direct_debits": 4915,
        "other": 4720,
        "total_amount": 2348,
        "created_at": "2020-11-21T04:16:48.000000Z",
        "updated_at": "2020-11-25T04:16:48.000000Z",
        "total_sum": 20168
    }
]

score 0 · Answer 3 · answered Nov 25 '20 at 04:40

0

SUM(larbour) as sum_labour, SUM(rent) as sum_rent, etc.

DB::table('table_name')
  ->select(DB::raw('SUM(labour) as sum_labour, DATE(created_at) as created_date'))
  ->groupBy(DB::raw('DATE(created_at)'))
  ->get();

answered Nov 25 '20 at 04:40

PHOKKINN KY

71
2

Php - Laravel :Sum of rows in group by date

3 Answers3