Sql script help

1

Hi Guys

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

city role
leeds sales
leeds sales
leeds HR
leeds IT Support
leeds Programmer
London IT Support
London Programmer
London Programmer
London sales
London sales
Manchester sales
Manchester sales
Manchester sales
Manchester sales
Manchester IT Support

the end result should look like the below

City total roles techincal none technical
Leeds 5 2 3
London 5 3 2
Manchester 5 1 4

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?

Thank you

2

The following query will work for you. However, ask yourself what you would need to do if a new role, for example, "Management" was added. With the query below, you would need to rewrite your query. There is a better way, which is to create a table of roles.

SELECT 
    city,
    Technical = SUM(CASE WHEN role IN ('IT Support','Programmer') THEN 1 ELSE 0 END),
    NonTechincal = SUM(CASE WHEN role IN ('sales', 'HR') THEN 1 ELSE 0 END)
FROM
    YourTable
GROUP BY
    city;
1 Like
3

You need to have a table that indicates what category a role is in, rather than hard-coding it in a SELECT.

That is, another table like this:
role / category
sales / non-technical
HR / non-technical
IT Support / technical
Programmer / technical
etc.

4

thanks

5

thank you