Test: UK designations

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

-- Which UK designation do the users indicate?
-- Based on their filled in location

    
WITH UsersCountry AS (
SELECT
    CASE
        WHEN lower(Location) LIKE '%ukr%' THEN 'Ukraine'  -- ukranian & typos
        
        -- United Kingdom
        
        WHEN lower(Location) LIKE lower('%United Kingdom%') THEN 'United Kingdom'
        
        --U.K.
        WHEN lower(Location) LIKE '%, u.k.%' THEN '%, u.k.%'
        WHEN lower(Location) LIKE '%,u.k. %' THEN '%,u.k. %'
        WHEN lower(Location) LIKE '%u.k.' THEN '%u.k.'
        
        --U.K
        WHEN lower(Location) LIKE '%, u.k%' THEN '%, u.k%'
        WHEN lower(Location) LIKE '%,u.k %' THEN '%,u.k %'
        WHEN lower(Location) LIKE '%u.k' THEN '%u.k'
        
        --UK
        WHEN lower(Location) LIKE '%, uk%' THEN '%, uk%'
        WHEN lower(Location) LIKE '%,uk %' THEN '%,uk %'
        WHEN lower(Location) LIKE '%uk' THEN '%uk'
        
        WHEN Location LIKE '%UK,%' THEN '%UK,% - Case Sens'
        
        WHEN lower(Location) LIKE lower('%Great Britain%') THEN 'Great Britain'
        --G.B.
        WHEN lower(Location) LIKE '%, g.b.%' THEN '%, g.b.%'
        WHEN lower(Location) LIKE '%,g.b. %' THEN '%,g.b. %'
        WHEN lower(Location) LIKE '%g.b.' THEN '%g.b.'
        
        --G.B
        WHEN lower(Location) LIKE '%, g.b%' THEN '%, g.b%'
        WHEN lower(Location) LIKE '%,g.b %' THEN '%,g.b %'
        WHEN lower(Location) LIKE '%g.b' THEN '%g.b'
        
        --GB
        WHEN lower(Location) LIKE '%, gb%' THEN '%, gb%'
        WHEN lower(Location) LIKE '%,gb %' THEN '%,gb %'
        WHEN lower(Location) LIKE '%gb' THEN '%gb'
        
 
        ELSE 'Other'
        
    END AS Country
FROM Users
      WHERE LEN(Location) > 1  and Reputation >= ##minimumReputation##
)

SELECT UsersCountry.Country,
count(1) AS 'count'
      FROM UsersCountry

GROUP BY UsersCountry.Country
--ORDER BY count(1) desc
ORDER BY Country asc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results