Q:

Laravel: Combining the results with UnionAll with count

Lets just say I have 2 column in a table resume_profiles
   current_location      city
    | Chennai  |    | Kolkatta   |
    | Mumbai   |    | Ahmaedabad |
    | Pune     |    | Kolkatta   |
    | Kolkatta |    | Pune       |

I need to combine these result into a single SET, So I'd have something like this:
   City        Aggregate
| Chennai    |    | 1 |
| Mumbai     |    | 1 |
| Pune       |    | 2 |
| Kolkatta   |    | 3 |
| Ahmaedabad |    | 1 |

Query:
$current_locations = ResumeProfile::selectRaw('current_location as city');

ResumeProfile::selectRaw('city,count(*) as aggregate')
           ->unionAll($current_locations)
           ->groupBy('city')->get();

when i use the above query i'm getting the below query with the exception
SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select city,count(*) as aggregate from resume_profiles group by city) union all (select current_location as city from resume_profiles))
I don't no how to achieve this

A:

try do this: 
  $current_locations = ResumeProfile::selectRaw('current_location as city');

 $subquery=ResumeProfile::selectRaw('city')
       ->unionAll($current_locations);

   DB::table(DB::raw("({$subquery->toSql()}) AS s"))
         ->selectRaw('s.city,count(*) as aggregate')
          ->groupBy('s.city')->get();

