Laravel: Combining the results with UnionAll with count

Viewed 2570

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

3 Answers
Related