jeudi 16 août 2018

Count all records grouped by year in laravel

I have a table named 'incidents' in MySQL with the data below

+-----+----------+---------------------+
| ID  | status   |     created_at      |
+-----+----------+---------------------+
|  1  | open     | 2017-08-14 16:15:24 |
|  2  | closed   | 2017-08-14 16:15:24 |
|  3  | open     | 2018-08-14 16:15:24 |
|  4  | invalid  | 2019-08-14 16:15:24 |
+-----+----------+---------------------+

I want to count all 'open' status per year. I tried:

$open = DB::table('incidents')
            ->select(DB::raw('count(*) as total'),DB::raw('YEAR(created_at) as year'))
            ->where('status','open')
            ->groupBy(DB::raw('YEAR(created_at)'))
            ->get();
return $open; 

It returns:

[{"total":1,"year":2017},{"total":1,"year":2018}]

What I want to return is the count in all of the year present in the database. Just like this

[{"total":1,"year":2017},{"total":1,"year":2018},{"total":0,"year":2019}]



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire