Wrong counting after grouping

Question

Have tables with wallets and services

Service

id and name

Wallets

id service_id balance

$statistic = Wallets::leftJoin('service', 'service.id', '=', 'wallets.service_id')->select('name as label', 'balance as value')->where('balance', '>', 0)->whereYear('wallets.updated_at', $now->year)->whereMonth('wallets.updated_at', $now->month)->get();

And get result

[{"label":"Service1","value":0.0711679},
 {"label":"Service1","value":0.015},
 {"label":"Service2","value":0.065572},
 {"label":"Service2","value":0.02},
 {"label":"Service3","value":0.0206064},
 {"label":"Service2","value":0.04399}]

but after ->groupBy('label'):

[{"label":"Service1","value":0.0711679},
{"label":"Service2","value":0.065572}
{"label":"Service3","value":0.0206064}]

get only first results

Answer 1

<?php 

// If you are referring count as total balance then you can do this
$statistic = Wallets::leftJoin('service', 'service.id', '=', 'wallets.service_id')
->where('balance', '>', 0)
->whereYear('wallets.updated_at', $now->year)
->whereMonth('wallets.updated_at', $now->month)
->select(
    \DB::raw('name as label'), 
    \DB::raw('SUM(balance) as value')
)
->groupBy('name')
->get();

// If you are referring count as total entries for each label then you can do this
$statistic = Wallets::leftJoin('service', 'service.id', '=', 'wallets.service_id')
->where('balance', '>', 0)
->whereYear('wallets.updated_at', $now->year)
->whereMonth('wallets.updated_at', $now->month)
->select(
    \DB::raw('name as label'), 
    \DB::raw('balance as value'),
    \DB::raw('count(*) as aggregate')
)
->groupBy('name')
->get();