I have a table (example of table below) and I am trying to write a query which will show city name , total number of roles by city, number of technical roles and number of non technical roles by city.
below is the example table
the end result should look like the below
|City||total roles||techincal||none technical|
total number of roles is a count of all the roles for each city, technical are a count roles if roles are 'IT Support' or 'Programmer' and none technical are a count roles if roles are 'sales' or 'HR'
I am having difficulty write the script to do this can any one help?