how to write this query in laravel query builder?
Because of the count, you will probably need to use an element of Raw expression Laravel QueryBuilder
$result = DB::table('cities c')
->join('patiens s','c.id','=','s.cityidp')
->select(DB::raw('c.cityname, count(distinct s.namep) as statecount'))
->groupBy('c.cityname')
->orderBy('statecount','desc')
->get();