Multiple selects in laravel 6.0

0, I have this code that works perfectly

$data = DB::table('incidencias')
   ->join('clientes_incid', 'clientes_incid.rif', '=', 'incidencias.rif')
   ->select(DB::raw('count(*) as gold, cat_cliente'))->where('cat_cliente', '=','Gold')
   ->groupBy('cat_cliente')
   ->get();

I want to make another select but with another condition, I tried with this but doesn’t work

$data = DB::table('incidencias')
   ->join('clientes_incid', 'clientes_incid.rif', '=', 'incidencias.rif')
   ->select(DB::raw('count(*) as gold, cat_cliente'))->where('cat_cliente', '=','Gold')
   ->select(DB::raw('count(*) as silver, cat_cliente'))->where('cat_cliente', '=','Silver')
   ->groupBy('cat_cliente')
   ->get();

Can somebody help me?

Answer

Group By cat_cliente, this will count gold, count silver…, in each records.

DB::table('incidencias')
   ->join('clientes_incid', 'clientes_incid.rif', '=', 'incidencias.rif')
   ->select(DB::raw('count(incidencias.id) as count'), cat_cliente)
   ->whereIn('cat_cliente', ['Gold', 'Silver'])
   ->groupBy('cat_cliente')
   ->get();

output =>

-----------------
 count | cat_cliente
------------------
   10  | Gold
------------------
    3  | Siliver
-----------------

You want to put them in one record, I think you can get it out and display it by loop. However, if you really want to get it in query, try this:

$gold_query = DB::table('incidencias')
   ->join('clientes_incid', 'clientes_incid.rif', '=', 'incidencias.rif')
   ->where('cat_cliente', 'Gold')
   ->selectRaw('COUNT(incidencias.id) AS gold', '0 AS silver');

$silver_query =  DB::table('incidencias')
   ->join('clientes_incid', 'clientes_incid.rif', '=', 'incidencias.rif')
   ->where('cat_cliente', 'Silver')
   ->selectRaw('0 AS gold, count(incidencias.id) AS silver');

$gold_silver = $gold_query->unionAll($silver_query);

DB::table(DB::raw("({$gold_silver->toSql()}) AS gs"))
          ->mergeBindings($gold_silver)
          ->selectRaw('SUM(gold) AS gold, SUM(silver) AS silver')
          ->get()

Output =>

----------------
 gold | silver | 
-----------------
   10  | 3    |
----------------