Users by location: Canada

0

Please login or register to vote for this query.

(click on this box to dismiss)

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

--- Which provinces are the users from?
-- Based on their filled in location

WITH UsersProvince AS (
SELECT 
          CASE                

                WHEN UPPER(Location) LIKE '%VANCOUVER, B%' THEN 'BC - Vancouver'
                WHEN UPPER(Location) LIKE '%VANCOUVER, C%' THEN 'BC - Vancouver'
                WHEN UPPER(Location) LIKE '%BRITISH COLUMBIA%' THEN 'British Columbia'
                WHEN UPPER(Location) LIKE '%B.C.%' THEN 'B.C.'
                WHEN UPPER(Location) LIKE '%, BC%' THEN 'BC - , BC'

                WHEN UPPER(Location) LIKE '%CALGARY%' THEN 'AB - Calgary'
                WHEN UPPER(Location) LIKE '%ALBERTA%' THEN 'Alberta'
                WHEN UPPER(Location) LIKE '%, AB%' THEN ' AB - , AB'

                WHEN UPPER(Location) LIKE '%REGINA%' THEN 'SK - Regina'
                WHEN UPPER(Location) LIKE '%SASKATOON%' THEN 'SK - Saskatoon'
                WHEN UPPER(Location) LIKE '%SASKATCHEWAN%' THEN 'Saskatchewan'
                WHEN UPPER(Location) LIKE '%SASK' THEN 'Sask'
                WHEN UPPER(Location) LIKE '%, SK%' THEN 'SK - , SK'

                WHEN UPPER(Location) LIKE '%WINNIPEG%' THEN 'MB - Winnipeg'
                WHEN UPPER(Location) LIKE '%MANITOBA%' THEN 'Manitoba'
                WHEN UPPER(Location) LIKE '%, MB%' THEN 'MB - , MB'

                WHEN UPPER(Location) LIKE '%TORONTO%' THEN 'On - Toronto'
                WHEN UPPER(Location) LIKE '%OTTAWA%' THEN 'On - Ottawa'
                WHEN UPPER(Location) LIKE '%MISSISSAUGA%' THEN 'On - Mississauga'
                WHEN UPPER(Location) LIKE '%BRAMPTON%' THEN 'On - Brampton'

                WHEN UPPER(Location) LIKE '%HAMILTON, ON%' THEN 'On - Hamilton, On'
                WHEN UPPER(Location) LIKE '%HAMILTON, CAN%' THEN 'On - Hamilton, Can'

                WHEN UPPER(Location) LIKE '%LONDON, ON%' THEN 'On - London, On'
                WHEN UPPER(Location) LIKE '%LONDON, CAN%' THEN 'On - London, Can'

                WHEN UPPER(Location) LIKE '%MARKHAM%' THEN 'On - Markham'
                WHEN UPPER(Location) LIKE '%VAUGHAN%' THEN 'On - Vaughan'
                
                WHEN UPPER(Location) LIKE '%ONTARIO, CAL%' THEN 'Ontario, Cal'
                WHEN UPPER(Location) LIKE '%ONTARIO, CAN%' THEN 'Ontario, Can'
                WHEN UPPER(Location) LIKE '%ONTARIO, CA%' THEN 'Ontario, Ca'
                WHEN UPPER(Location) LIKE '%ONTARIO, U%' THEN 'Ontario, U'
                WHEN UPPER(Location) LIKE '%, ON%' THEN 'ON - , ON'

                WHEN UPPER(Location) LIKE '%MONTREAL%' THEN 'QC - Montreal'
                WHEN UPPER(Location) LIKE '%QUEBEC%' THEN 'Quebec'
                WHEN UPPER(Location) LIKE '%QC%' THEN 'QC'

                WHEN UPPER(Location) LIKE '%Saint John, N%' THEN 'NB - Saint John'
                WHEN UPPER(Location) LIKE '%NEW BRUNSWICK%' THEN 'New Brunswick'
                WHEN UPPER(Location) LIKE '%, NB%' THEN 'NB - , NB'

                WHEN UPPER(Location) LIKE '%HALIFAX, N%' THEN 'NS - Halifax'
                WHEN UPPER(Location) LIKE '%NOVA SCOTIA%' THEN 'Nova Scotia'
                WHEN UPPER(Location) LIKE '%, NS%' THEN 'NS - , NS'

                WHEN UPPER(Location) LIKE '%PRINCE EDWARD ISLAND%' THEN 'Prince Edward Island'
                WHEN UPPER(Location) LIKE '%PEI' THEN 'PEI'
                WHEN UPPER(Location) LIKE '%, PE%' THEN 'PE - , PE'

                WHEN UPPER(Location) LIKE '%NEWFOUNDLAND%' THEN 'Newfoundland'
                WHEN UPPER(Location) LIKE '%, NF%' THEN ', NF'

                WHEN UPPER(Location) LIKE '%YUKON%' THEN 'Yukon'
                WHEN UPPER(Location) LIKE '%YK%' THEN 'YK'

                WHEN UPPER(Location) LIKE '%NORTHWEST TERRITORIES%' THEN 'Northwest Territories'
                -- WHEN UPPER(Location) LIKE '%, NT%' THEN 'NT - , NT'

                WHEN UPPER(Location) LIKE '%NUNAVUT%' THEN 'Nunavut'
                WHEN UPPER(Location) LIKE '%, NU%' THEN 'NU - , NU'

                WHEN UPPER(Location) LIKE '%CANADA%' THEN 'Canada'
     
             ELSE 'Z - Not Canada or Unknown'
         END AS Province
FROM Users
      WHERE LEN(Location) > 2
)

SELECT UsersProvince.Province
      FROM UsersProvince

GROUP BY UsersProvince.Province
-- ORDER BY count(1) desc
ORDER BY Province asc

Enter Parameters

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