mercredi 27 juillet 2016

Group by and having query in laravel

I need a sum of unit for that devices 1,2 on the base of date but i'm trying with my query but result is not right.And i don't when having function is used.so please help me.My query,schema sample and expected result is given below

Database data

|--------------------------------------
|device_id  |   unit    | date       
|--------------------------------------
| 1         |   2       | 2016-07-01
---------------------------------------
| 1         |   3       | 2016-07-02
--------------------------------------
| 2         |   1       | 2016-07-02
--------------------------------------
| 2         |   4       | 2016-07-10
--------------------------------------
| 1         |   3       | 2016-07-10
-------------------------------------
| 2         |   1       | 2016-07-21
--------------------------------------
| 2         |   2       | 2016-07-23
--------------------------------------
| 1         |   3       | 2016-07-25
--------------------------------------
| 2         |   2       | 2016-07-27
--------------------------------------
| 1         |   3       | 2016-07-27

My query

 $startMonth=Carbon::today()->startOfMonth()->toDateString();
  $now=Carbon::today()->toDateString();
$consumption= PowerConsumption::groupBY(['date'])
            ->selectRaw('sum(unit) as yAxis,date')
            ->whereIn('device_id',[1,2])
            ->whereBetween('date',[$startMonth,$now])->get();

And Expected Result

[
{
    yAxis:2,
    date:2016-07-01
},
{
    yAxis:4, //3+1
    date:2016-07-02
},
{
    yAxis:7, //4+3
    date:2016-07-10
},
{
    yAxis:1, 
    date:2016-07-21
},
{
    yAxis:2, 
    date:2016-07-23
},
{
    yAxis:3, 
    date:2016-07-25
},
{
    yAxis:5, //2+3
    date:2016-07-27
}
]

Please help me by modify my query and query return above expected result



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire