0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

SELECT 
    (CASE
      WHEN LOWER(Location) LIKE '%afghanistan%' THEN 'Afghanistan'
      WHEN LOWER(Location) LIKE '%albania%' THEN 'Albania'
      WHEN LOWER(Location) LIKE '%algeria%' THEN 'Algeria'
      WHEN LOWER(Location) LIKE '%andorra%' THEN 'Andorra'
      WHEN LOWER(Location) LIKE '%angola%' THEN 'Angola'
      WHEN LOWER(Location) LIKE '%antigua%' OR LOWER(Location) LIKE '%barbuda%' THEN 'Antigua and Barbuda'
      WHEN LOWER(Location) LIKE '%argentina%' THEN 'Argentina'
      WHEN LOWER(Location) LIKE '%armenia%' THEN 'Armenia'
      WHEN LOWER(Location) LIKE '%australia%' THEN 'Australia'
      WHEN LOWER(Location) LIKE '%austria%' THEN 'Austria'
      WHEN LOWER(Location) LIKE '%azerbaijan%' THEN 'Azerbaijan'
      WHEN LOWER(Location) LIKE '%bahamas%' THEN 'Bahamas'
      WHEN LOWER(Location) LIKE '%bahrain%' THEN 'Bahrain'
      WHEN LOWER(Location) LIKE '%bangladesh%' THEN 'Bangladesh'
      WHEN LOWER(Location) LIKE '%barbados%' THEN 'Barbados'
      WHEN LOWER(Location) LIKE '%belarus%' THEN 'Belarus'
      WHEN LOWER(Location) LIKE '%belgium%' THEN 'Belgium'
      WHEN LOWER(Location) LIKE '%belize%' THEN 'Belize'
      WHEN LOWER(Location) LIKE '%benin%' THEN 'Benin'
      WHEN LOWER(Location) LIKE '%bhutan%' THEN 'Bhutan'
      WHEN LOWER(Location) LIKE '%bolivia%' THEN 'Bolivia'
      WHEN LOWER(Location) LIKE '%bosnia%' THEN 'Bosnia and Herzegovina'
      WHEN LOWER(Location) LIKE '%botswana%' THEN 'Botswana'
      WHEN LOWER(Location) LIKE '%brazil%' THEN 'Brazil'
      WHEN LOWER(Location) LIKE '%brunei%' THEN 'Brunei'
      WHEN LOWER(Location) LIKE '%bulgaria%' THEN 'Bulgaria'
      WHEN LOWER(Location) LIKE '%burkina%' THEN 'Burkina Faso'
      WHEN LOWER(Location) LIKE '%burundi%' THEN 'Burundi'
      WHEN LOWER(Location) LIKE '%ivoire%' THEN 'Côte dIvoire'
      WHEN LOWER(Location) LIKE '%cabo verde%' THEN 'Cabo Verde'
      WHEN LOWER(Location) LIKE '%cambodia%' THEN 'Cambodia'
      WHEN LOWER(Location) LIKE '%cameroon%' THEN 'Cameroon'
      WHEN LOWER(Location) LIKE '%canada%' THEN 'Canada'
      WHEN LOWER(Location) LIKE '%central african republic%' THEN 'Central African Republic'
      WHEN LOWER(Location) LIKE '%chad%' THEN 'Chad'
      WHEN LOWER(Location) LIKE '%chile%' THEN 'Chile'
      WHEN LOWER(Location) LIKE '%china%' THEN 'China'
      WHEN LOWER(Location) LIKE '%colombia%' THEN 'Colombia'
      WHEN LOWER(Location) LIKE '%comoros%' THEN 'Comoros'
      WHEN LOWER(Location) LIKE '%congo%' THEN 'Congo'
      WHEN LOWER(Location) LIKE '%costa rica%' THEN 'Costa Rica'
      WHEN LOWER(Location) LIKE '%croatia%' THEN 'Croatia'
      WHEN LOWER(Location) LIKE '%cuba%' THEN 'Cuba'
      WHEN LOWER(Location) LIKE '%cyprus%' THEN 'Cyprus'
      WHEN LOWER(Location) LIKE '%czech%' THEN 'Czechia (Czech Republic)'
      WHEN LOWER(Location) LIKE '%congo%' THEN 'Democratic Republic of the Congo'
      WHEN LOWER(Location) LIKE '%denmark%' THEN 'Denmark'
      WHEN LOWER(Location) LIKE '%djibouti%' THEN 'Djibouti'
      WHEN LOWER(Location) LIKE '%dominica%' THEN 'Dominican Republic'
      WHEN LOWER(Location) LIKE '%ecuador%' THEN 'Ecuador'
      WHEN LOWER(Location) LIKE '%egypt%' THEN 'Egypt'
      WHEN LOWER(Location) LIKE '%salvador%' THEN 'El Salvador'
      WHEN LOWER(Location) LIKE '%equatorial guinea%' THEN 'Equatorial Guinea'
      WHEN LOWER(Location) LIKE '%eritrea%' THEN 'Eritrea'
      WHEN LOWER(Location) LIKE '%estonia%' THEN 'Estonia'
      WHEN LOWER(Location) LIKE '%eswatini%' OR LOWER(Location) LIKE '%swaziland%' THEN 'Eswatini (fmr. "Swaziland")'
      WHEN LOWER(Location) LIKE '%ethiopia%' THEN 'Ethiopia'
      WHEN LOWER(Location) LIKE '%fiji%' THEN 'Fiji'
      WHEN LOWER(Location) LIKE '%finland%' THEN 'Finland'
      WHEN LOWER(Location) LIKE '%france%' THEN 'France'
      WHEN LOWER(Location) LIKE '%gabon%' THEN 'Gabon'
      WHEN LOWER(Location) LIKE '%gambia%' THEN 'Gambia'
      WHEN LOWER(Location) LIKE '%georgia%' THEN 'Georgia'
      WHEN LOWER(Location) LIKE '%germany%' THEN 'Germany'
      WHEN LOWER(Location) LIKE '%ghana%' THEN 'Ghana'
      WHEN LOWER(Location) LIKE '%greece%' THEN 'Greece'
      WHEN LOWER(Location) LIKE '%grenada%' THEN 'Grenada'
      WHEN LOWER(Location) LIKE '%guatemala%' THEN 'Guatemala'
      WHEN LOWER(Location) LIKE '%guinea%' THEN 'Guinea'
      WHEN LOWER(Location) LIKE '%bissau%' THEN 'Guinea-Bissau'
      WHEN LOWER(Location) LIKE '%guyana%' THEN 'Guyana'
      WHEN LOWER(Location) LIKE '%haiti%' THEN 'Haiti'
      WHEN LOWER(Location) LIKE '%honduras%' THEN 'Honduras'
      WHEN LOWER(Location) LIKE '%hungary%' THEN 'Hungary'
      WHEN LOWER(Location) LIKE '%iceland%' THEN 'Iceland'
      WHEN LOWER(Location) LIKE '%india%' THEN 'India'
      WHEN LOWER(Location) LIKE '%indonesia%' THEN 'Indonesia'
      WHEN LOWER(Location) LIKE '%iran%' THEN 'Iran'
      WHEN LOWER(Location) LIKE '%iraq%' THEN 'Iraq'
      WHEN LOWER(Location) LIKE '%ireland%' THEN 'Ireland'
      WHEN LOWER(Location) LIKE '%israel%' THEN 'Israel'
      WHEN LOWER(Location) LIKE '%italy%' THEN 'Italy'
      WHEN LOWER(Location) LIKE '%jamaica%' THEN 'Jamaica'
      WHEN LOWER(Location) LIKE '%japan%' THEN 'Japan'
      WHEN LOWER(Location) LIKE '%jordan%' THEN 'Jordan'
      WHEN LOWER(Location) LIKE '%kazakhstan%' THEN 'Kazakhstan'
      WHEN LOWER(Location) LIKE '%kenya%' THEN 'Kenya'
      WHEN LOWER(Location) LIKE '%kiribati%' THEN 'Kiribati'
      WHEN LOWER(Location) LIKE '%kuwait%' THEN 'Kuwait'
      WHEN LOWER(Location) LIKE '%kyrgyzstan%' THEN 'Kyrgyzstan'
      WHEN LOWER(Location) LIKE '%laos%' THEN 'Laos'
      WHEN LOWER(Location) LIKE '%latvia%' THEN 'Latvia'
      WHEN LOWER(Location) LIKE '%lebanon%' THEN 'Lebanon'
      WHEN LOWER(Location) LIKE '%lesotho%' THEN 'Lesotho'
      WHEN LOWER(Location) LIKE '%liberia%' THEN 'Liberia'
      WHEN LOWER(Location) LIKE '%libya%' THEN 'Libya'
      WHEN LOWER(Location) LIKE '%liechtenstein%' THEN 'Liechtenstein'
      WHEN LOWER(Location) LIKE '%lithuania%' THEN 'Lithuania'
      WHEN LOWER(Location) LIKE '%luxembourg%' THEN 'Luxembourg'
      WHEN LOWER(Location) LIKE '%madagascar%' THEN 'Madagascar'
      WHEN LOWER(Location) LIKE '%malawi%' THEN 'Malawi'
      WHEN LOWER(Location) LIKE '%malaysia%' THEN 'Malaysia'
      WHEN LOWER(Location) LIKE '%maldives%' THEN 'Maldives'
      WHEN LOWER(Location) LIKE '%mali%' THEN 'Mali'
      WHEN LOWER(Location) LIKE '%malta%' THEN 'Malta'
      WHEN LOWER(Location) LIKE '%marshall%' THEN 'Marshall Islands'
      WHEN LOWER(Location) LIKE '%mauritania%' THEN 'Mauritania'
      WHEN LOWER(Location) LIKE '%mauritius%' THEN 'Mauritius'
      WHEN LOWER(Location) LIKE '%mexico%' THEN 'Mexico'
      WHEN LOWER(Location) LIKE '%micronesia%' THEN 'Micronesia'
      WHEN LOWER(Location) LIKE '%moldova%' THEN 'Moldova'
      WHEN LOWER(Location) LIKE '%monaco%' THEN 'Monaco'
      WHEN LOWER(Location) LIKE '%mongolia%' THEN 'Mongolia'
      WHEN LOWER(Location) LIKE '%montenegro%' THEN 'Montenegro'
      WHEN LOWER(Location) LIKE '%morocco%' THEN 'Morocco'
      WHEN LOWER(Location) LIKE '%mozambique%' THEN 'Mozambique'
      WHEN LOWER(Location) LIKE '%myanmar%' OR LOWER(Location) LIKE '%burma%' THEN 'Myanmar (formerly Burma)'
      WHEN LOWER(Location) LIKE '%namibia%' THEN 'Namibia'
      WHEN LOWER(Location) LIKE '%nauru%' THEN 'Nauru'
      WHEN LOWER(Location) LIKE '%nepal%' THEN 'Nepal'
      WHEN LOWER(Location) LIKE '%netherlands%' THEN 'Netherlands'
      WHEN LOWER(Location) LIKE '%zealand%' THEN 'New Zealand'
      WHEN LOWER(Location) LIKE '%nicaragua%' THEN 'Nicaragua'
      WHEN LOWER(Location) LIKE '%niger%' THEN 'Niger'
      WHEN LOWER(Location) LIKE '%nigeria%' THEN 'Nigeria'
      WHEN LOWER(Location) LIKE '%macedonia%' THEN 'North Macedonia'
      WHEN LOWER(Location) LIKE '%norway%' THEN 'Norway'
      WHEN LOWER(Location) LIKE '%oman%' THEN 'Oman'
      WHEN LOWER(Location) LIKE '%pakistan%' THEN 'Pakistan'
      WHEN LOWER(Location) LIKE '%palau%' THEN 'Palau'
      WHEN LOWER(Location) LIKE '%palestine%' THEN 'Palestine State'
      WHEN LOWER(Location) LIKE '%panama%' THEN 'Panama'
      WHEN LOWER(Location) LIKE '%papua new guinea%' OR LOWER(Location) LIKE '%png%' THEN 'Papua New Guinea'
      WHEN LOWER(Location) LIKE '%paraguay%' THEN 'Paraguay'
      WHEN LOWER(Location) LIKE '%peru%' THEN 'Peru'
      WHEN LOWER(Location) LIKE '%philippines%' THEN 'Philippines'
      WHEN LOWER(Location) LIKE '%poland%' THEN 'Poland'
      WHEN LOWER(Location) LIKE '%portugal%' THEN 'Portugal'
      WHEN LOWER(Location) LIKE '%qatar%' THEN 'Qatar'
      WHEN LOWER(Location) LIKE '%romania%' THEN 'Romania'
      WHEN LOWER(Location) LIKE '%russia%' THEN 'Russia'
      WHEN LOWER(Location) LIKE '%rwanda%' THEN 'Rwanda'
      WHEN LOWER(Location) LIKE '%samoa%' THEN 'Samoa'
      WHEN LOWER(Location) LIKE '%san marino%' THEN 'San Marino'
      WHEN LOWER(Location) LIKE '%principe%' THEN 'Sao Tome and Principe'
      WHEN LOWER(Location) LIKE '%saudi%' THEN 'Saudi Arabia'
      WHEN LOWER(Location) LIKE '%senegal%' THEN 'Senegal'
      WHEN LOWER(Location) LIKE '%serbia%' THEN 'Serbia'
      WHEN LOWER(Location) LIKE '%seychelles%' THEN 'Seychelles'
      WHEN LOWER(Location) LIKE '%sierra leone%' THEN 'Sierra Leone'
      WHEN LOWER(Location) LIKE '%singapore%' THEN 'Singapore'
      WHEN LOWER(Location) LIKE '%slovakia%' THEN 'Slovakia'
      WHEN LOWER(Location) LIKE '%slovenia%' THEN 'Slovenia'
      WHEN LOWER(Location) LIKE '%solomon islands%' THEN 'Solomon Islands'
      WHEN LOWER(Location) LIKE '%somalia%' THEN 'Somalia'
      WHEN LOWER(Location) LIKE '%south africa%' THEN 'South Africa'
      WHEN LOWER(Location) LIKE '%south korea%' THEN 'South Korea'
      WHEN LOWER(Location) LIKE '%south sudan%' THEN 'South Sudan'
      WHEN LOWER(Location) LIKE '%spain%' THEN 'Spain'
      WHEN LOWER(Location) LIKE '%sri lanka%' THEN 'Sri Lanka'
      WHEN LOWER(Location) LIKE '%sudan%' THEN 'Sudan'
      WHEN LOWER(Location) LIKE '%suriname%' THEN 'Suriname'
      WHEN LOWER(Location) LIKE '%sweden%' THEN 'Sweden'
      WHEN LOWER(Location) LIKE '%switzerland%' THEN 'Switzerland'
      WHEN LOWER(Location) LIKE '%syria%' THEN 'Syria'
      WHEN LOWER(Location) LIKE '%tajikistan%' THEN 'Tajikistan'
      WHEN LOWER(Location) LIKE '%tanzania%' THEN 'Tanzania'
      WHEN LOWER(Location) LIKE '%thailand%' THEN 'Thailand'
      WHEN LOWER(Location) LIKE '%timor%' THEN 'Timor-Leste'
      WHEN LOWER(Location) LIKE '%togo%' THEN 'Togo'
      WHEN LOWER(Location) LIKE '%tonga%' THEN 'Tonga'
      WHEN LOWER(Location) LIKE '%trinidad%' OR LOWER(Location) LIKE '%tobago%' THEN 'Trinidad and Tobago'
      WHEN LOWER(Location) LIKE '%tunisia%' THEN 'Tunisia'
      WHEN LOWER(Location) LIKE '%turkey%' THEN 'Turkey'
      WHEN LOWER(Location) LIKE '%turkmenistan%' THEN 'Turkmenistan'
      WHEN LOWER(Location) LIKE '%tuvalu%' THEN 'Tuvalu'
      WHEN LOWER(Location) LIKE '%uganda%' THEN 'Uganda'
      WHEN LOWER(Location) LIKE '%ukraine%' THEN 'Ukraine'
      WHEN LOWER(Location) LIKE '%united arab emirates%' OR LOWER(Location) LIKE '%uae%' THEN 'United Arab Emirates'
      WHEN LOWER(Location) LIKE '%united kingdom%' OR LOWER(Location) LIKE '%uk%' THEN 'United Kingdom'
      WHEN LOWER(Location) LIKE '%united states of america%' OR LOWER(Location) LIKE '%usa%' THEN 'United States of America'
      WHEN LOWER(Location) LIKE '%uruguay%' THEN 'Uruguay'
      WHEN LOWER(Location) LIKE '%uzbekistan%' THEN 'Uzbekistan'
      WHEN LOWER(Location) LIKE '%vanuatu%' THEN 'Vanuatu'
      WHEN LOWER(Location) LIKE '%venezuela%' THEN 'Venezuela'
      WHEN LOWER(Location) LIKE '%vietnam%' THEN 'Vietnam'
      WHEN LOWER(Location) LIKE '%yemen%' THEN 'Yemen'
      WHEN LOWER(Location) LIKE '%zambia%' THEN 'Zambia'
      WHEN LOWER(Location) LIKE '%zimbabwe%' THEN 'Zimbabwe'
    END) as country,
    COUNT(u.Id) AS number
FROM
    Users AS u
    INNER JOIN Posts AS p ON p.OwnerUserId = u.id
    INNER JOIN PostTags AS pt ON pt.PostId = p.ParentId
    INNER JOIN Tags AS t ON t.id = pt.TagId
WHERE 
    t.TagName LIKE '%[Rr]uby%'
    AND
    u.Reputation >= 1000
GROUP BY Location
ORDER BY number DESC

fork query

edited oct 28

created oct 27 anonymous