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