LARAVEL count with conditions

Question

I have a table called transactions I get data from it as statistics grouped by year and month and total but I need get also success and failed depend on state field.

the issue here is that the success and field return same value of total, like that

{
"count": 36,
"success": 36,
"failed": 36,
"date": "01-2022",
"year": 2022,
"month": 1
},

the logic is

return $this->transactions()
            ->select(
                DB::raw('count(state) as count'),

                DB::raw('count( state="success" ) as success'),
                DB::raw('count( state!="success" ) as failed'),
                
                DB::raw("DATE_FORMAT(created_at, '%m-%Y') as date"),
                DB::raw('YEAR(created_at) year, MONTH(created_at) month')
            )

            ->where(function ($query) use ($start, $end) {
                return $query->where('created_at', '>=', $start)
                    ->orWhere('created_at', '>=', $end);
            })

            

            ->groupBy('year', 'month')
            ->orderBy('created_at', 'asc')
            ->get();

Answer 1

you can use this :

$this->transactions()
    ->select(
        DB::raw('count(state) as count'),

        DB::raw('SUM(case when state = "success" then 1 else 0 end) as success'),

        DB::raw('SUM(case when state != "success" then 1 else 0 end) as failed'),

        DB::raw("DATE_FORMAT(created_at, '%m-%Y') as date"),
        DB::raw('YEAR(created_at) year, MONTH(created_at) month')
    )

    ->where(function ($query) use ($start, $end) {
        return $query->where('created_at', '>=', $start)
            ->orWhere('created_at', '>=', $end);
    })



    ->groupBy('year', 'month')
    ->orderBy('created_at', 'asc')
    ->get();

reformat your query to

        $this->transactions()
        ->selectRaw(
            '
                count(state) as count,
                SUM(case when state = "success" then 1 else 0 end) as success,
                SUM(case when state != "success" then 1 else 0 end) as failed,
                DATE_FORMAT(created_at, "%m-%Y") as date,
                YEAR(created_at) year, MONTH(created_at) month
            '
        )
        ->where('created_at', '>=', $start)
        ->orWhere('created_at', '>=', $end)
        ->groupBy('year', 'month')
        ->orderBy('created_at')
        ->get();