Wednesday 18 September 2013

Group countries together and count

Group countries together and count

I have a table like this:

-------------------------------
EMP ID|Country |Emp Level |
------|-----------|-----------|
102 |UK |Staff |
103 |US |Admin Staff|
104 |CA |Staff |
105 |NL |Admin Staff|
106 |MN |Intern |
107 |IN |Staff |
108 |UK |Staff |
109 |US |Admin Staff|
110 |IN |Admin Staff|
------------------------------
I need to count number of employees in each category in each country given
following condition: If country is not in ('UK' or 'US' or 'CA') then
consider it as 'Global'. SO our answer should be:

------------------------------
|Country |Emp Level |Count|
|-----------|-----------|-----
|UK |Staff |2
|US |Admin Staff|2
|CA |Staff |1
|Global |Admin Staff|2
|Global |Intern |1
|Global |Staff |1
So far I can count number of staff in each category, in each country but
cannot club the countries not in given set and count & display them as
global.

No comments:

Post a Comment