Laravel Select Count Where Query

Question

I have Orders table and status column in my DB, the status is stored as [1,2,3,4] which is corresponding at frontend to [issued, pending, on thew ay, delivered] now I need to get the count of each status between 2 dates,

Please Note : I need the count of each status like,

issued : 80 pending : 50 on the way : 20 delivered : 170

I tried below code but don't no how to accomplish my needs

$account = DB::table('order')
                    ->whereBetween('created_at',[$fromdate, $todate])
                    ->select(DB::raw('COUNT(order.status) as total'))
                    ->get();
                    return response()->json($account,200);

Any help will be much appreciated

possible duplicate of: https://stackoverflow.com/questions/13223512/how-to-select-count-with-laravels-fluent-query-builder — loic.lopez, Apr 15 '19 at 15:45

score 2 · Accepted Answer · edited Apr 15 '19 at 16:15

2

$account = DB::table('order')
    ->select(DB::raw('COUNT(order.id) as total'),'status')
    ->whereBetween('created_at',[$fromdate, $todate])
    ->whereIn('status',[1,2,3,4])
    ->groupBy('status')
    ->get();

return response()->json($account,200);

edited Apr 15 '19 at 16:15

Tharaka Dilshan

4,371
3
14
28

answered Apr 15 '19 at 16:14

Sanjula Madumal Hewage

150
8

Thanks, this is what i was looking for – Mohammed Riyadh Apr 15 '19 at 16:43

score 0 · Answer 2 · answered Apr 15 '19 at 16:14

0

DB::table('order')
    ->select(DB::raw('COUNT(*) as total')
    ->whereBetween('created_at',[$fromdate, $todate])
    ->groupBy('status')
    ->get();

answered Apr 15 '19 at 16:14

gbalduzzi

9,356
28
58

Laravel Select Count Where Query

2 Answers2