如何通过在Laravel 5.7中的同一列中执行不同的查询来创建不同的别名

Question

I have a table in MySQL where I store indexes, and I need to count a column of indices by bringing an alias for each count.

I tried the following code but it does not work, and as I am new to Laravel, I'm not sure how I can build this query

$data = Baralho::select(
        DB::raw("YEAR(data_entrada) as ano"),
        DB::raw("count(id_situacao) as inseridos, id_situacao"),
        DB::raw("count(id_situacao = 2) as presos"),
        DB::raw("count(id_situacao = 3) as mortos"),
        DB::raw("count(id_situacao = 4) as retirados")
    )->groupBy("ano")->get();
    $geral[] = ['Ano', 'Inseridos', 'Presos', 'Mortos', 'Retirados'];
    //$anos = $data->pluck('ano');
    foreach($data as $key => $value){
        $geral[++$key] = [$value->ano, $value->inseridos, $value->presos, $value->mortos, $value->retirados ];
    }
    dd($geral);

I need it to count the indexes and return a result for each alias, for example, if in the column id_situacao the index is 2 it counts all 2 indexes and brings me the result with the alias presos so that I can insert those results in my array $geral[]

dd($geral) Result

Edit:
I tried with the following code:

$data = DB::select(
            DB::raw('(SELECT YEAR(data_entrada) as ano FROM baralho)'),
            DB::raw('(SELECT count(id_situacao) as inseridos FROM baralho)'),
            DB::raw('(SELECT count(id_situacao) as presos FROM baralho WHERE id_situacao = 2)'),
            DB::raw('(SELECT count(id_situacao) as mortos FROM baralho WHERE id_situacao = 3)'),
            DB::raw('(SELECT count(id_situacao) as retirados FROM baralho WHERE id_situacao = 4)')
        )->groupBy('ano')->get();;
        $geral[] = ['Ano', 'Inseridos', 'Presos', 'Mortos', 'Retirados'];
        //$anos = $data->pluck('ano');
        //dd($anos);
        foreach($data as $key => $value){
            $geral[++$key] = [$value->ano, $value->inseridos, $value->presos, $value->mortos, $value->retirados];
            //dd($value);
        }
        dd($geral);

but I received the error:

Symfony \ Component \ Debug \ Exception \ FatalThrowableError (E_RECOVERABLE_ERROR) Argument 1 passed to Illuminate\Database\Connection::prepareBindings() must be of the type array, object given, called in /home/u185216683/domains/disquedenuncia.com/sysdados/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 328

Answer 1

Well, I was able to solve my problem in the following way:

$data = Baralho::select(
            DB::raw("YEAR(data_entrada) as ano"),
            DB::raw("count(id_situacao) as inseridos, id_situacao"),
            DB::raw("count(case when id_situacao = 2 then 1 end) as presos"),
            DB::raw("count(case when id_situacao = 3 then 1 end) as mortos"),
            DB::raw("count(case when id_situacao = 4 then 1 end) as retirados")
        )->groupBy("ano")->get();
        $geral[] = ["Ano", "Inseridos", "Presos", "Mortos", "Retirados"];
        foreach($data as $key => $value){
            $geral[++$key] = [$value->ano, $value->inseridos, $value->presos, $value->mortos, $value->retirados ];
        }  

Only with the CASE clause I was able to count the id_situacao fields by grouping by id and returning an alias for each type