mardi 31 mai 2016

Laravel SQL groupBy and sum

I here is my tables relation:

reservations id, date, etc...
reservation_service_info service_info_id, reservation_id
services_info price etc...

I am trying to sum reservation price by day. Here is the code and result:

return $query->selectRaw('DATE(start_time) AS date')
        ->selectRaw("(SELECT price FROM `services_info` inner join `reservation_service_info` on `services_info`.`id` = `reservation_service_info`.`service_info_id` where `reservation_service_info`.`reservation_id` = reservations.id) as price")
        ->orderBy('date', 'ASC')
        ->get('price', 'date')

Result:

[
{
"date": "2016-06-01",
"price": "345.00"
},
{
"date": "2016-06-01",
"price": "90.00"
},
{
"date": "2016-06-01",
"price": "222.00"
},
{
"date": "2016-06-02",
"price": "393.00"
},
{
"date": "2016-06-02",
"price": "142.00"
}
]

When I add groupBy('date') in the query it groups it by dosent SUM(price)

[
{
"date": "2016-06-01",
"price": "345.00"
},
{
"date": "2016-06-02",
"price": "393.00"
}
]



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire