How count by the first letters in Laravel Query Builder?

Question

I want to make a count by the first letters... I have this column

I would like to count each OE rows and each GICS rows

I'm working with this query

$data4 = DB::table('incidencias')
   ->select(DB::raw('grupo_asig as grupo_asig'), DB::raw('count(*) as number'))
   ->whereNotIn('grupo_asig', [''])
   ->groupBy('grupo_asig')
   ->orderBy('number', 'desc')
   ->get();

Answer 1

Use CASE WHEN and count the field like OE and ASIG

$data4 = DB::table('incidencias')
           ->select(DB::raw("(CASE WHEN grupo_asig LIKE 'OE%' THEN 'OE'
                                   WHEN grupo_asig LIKE 'GICS%' THEN 'GICS'
                              END) AS grupo_asig_type"), 
                    DB::raw('COUNT(*) as number'))
           ->whereNotIn('grupo_asig', [''])
           ->groupBy('grupo_asig_type')
           ->orderBy('number', 'desc')
           ->get();