0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Tridion developers and administrators

--select sum(Cnt) from (
select Location, Cnt, CleanedLocation from
  (select Location, Cnt, 

  case 

    when Location like '%USA%' then 'USA'
    when Location like '%United States%' then 'USA'
    when Location like '%San Francisco%' then 'USA'
    when Location like '%India%' then 'India'
    when Location like '%Bangalore%' then 'India'
    when Location like '%Germany%' then 'Germany'
    when Location like '%China%' then 'China'
    when Location like '%France%' then 'France'
    when Location like '%United Kingdom%' then 'United Kingdom'
    when Location like '%London%' then 'United Kingdom'
    when Location like '%UK%' then 'United Kingdom'
    when Location like '%Philippines%' then 'Philippines'
    when Location like '%Indonesia%' then 'Indonesia'
    when Location like '%Singapore%' then 'Singapore'
    when Location like '%Pakistan%' then 'Pakistan'
    when Location like '%Canada%' then 'Canada'
    when Location like '%Israel%' then 'Israel'
    when Location like '%Russia%' then 'Russia'
    when Location like '%Netherlands%' then 'Netherlands'
    when Location like '%Egypt%' then 'Egypt'
    when Location like '%Bangladesh%' then 'Bangladesh'
    when Location like '%Brazil%' then 'Brazil'
    when Location like '%Brasil%' then 'Brazil'
    when Location like '%Iran%' then 'Iran'
    when Location like '%Poland%' then 'Poland'
    when Location like '%Australia%' then 'Australia'
    when Location like '%Ukraine%' then 'Ukraine'
    when Location like '%Vietnam%' then 'Vietnam'
    when Location like '%Việt Nam%' then 'Vietnam'
    when Location like '%Italy%' then 'Italy'
    when Location like '%Sweden%' then 'Sweden'
    when Location like '%Malaysia%' then 'Malaysia'
    when Location like '%Spain%' then 'Spain'
    when Location like '%Turkey%' then 'Turkey'
    when Location like '%Türkiye%' then 'Turkey'
    when Location like '%Nam%' then 'Vietnam'
    when Location like '%Belgium%' then 'Belgium'
    when Location like '%Nigeria%' then 'Nigeria'
    when Location like '%Sri Lanka%' then 'Sri Lanka'
    when Location like '%Switzerland%' then 'Switzerland'
    when Location like '%Hong Kong%' then 'Hong Kong'
    when Location like '%South Africa%' then 'South Africa'
    when Location like '%Japan%' then 'Japan'
    when Location like '%Denmark%' then 'Denmark'
    when Location like '%Portugal%' then 'Portugal'
    when Location like '%Ireland%' then 'Ireland'
    when Location like '%Taiwan%' then 'Taiwan'
    when Location like '%Argentina%' then 'Argentina'
    when Location like '%china%' then 'China'
    when Location like '%Morocco%' then 'Morocco'
    when Location like '%Beijing%' then 'China'
    when Location like '%Seoul%' then 'South Korea'
    when Location like '%Korea%' then 'South Korea'
	when Location like '%Romania%' then 'Romania'
	when Location like '%Moscow%' then 'Russia'
	when Location like '%Madrid%' then 'Spain'
	when Location like '%Thailand%' then 'Thailand'
	when Location like '%Seattle%' then 'USA'
	when Location like '%California%' then 'USA'
	when Location like '%Kenya%' then 'Kenya'
	when Location like '%Italia%' then 'Italy'
	when Location like '%Greece%' then 'Greece'
	when Location like '%Norway%' then 'Norway'
	when Location like '%Austria%' then 'Austria'
	when Location like '%Dubai%' then 'UAE'
	when Location like '%New Zealand%' then 'New Zealand'
	when Location like '%Berlin%' then 'Germany'
	when Location like '%Chile%' then 'Chile'
	when Location like '%Belarus%' then 'Belarus'
	when Location like '%Warszawa%' then 'Poland'
	when Location like '%Colombia%' then 'Colombia'
	when Location like '%Pune%' then 'India'
	when Location like '%Chicago%' then 'USA'
	when Location like '%Finland%' then 'Finland'
	when Location like '%Bulgaria%' then 'Bulgaria'
	when Location like '%Mexico%' then 'Mexico'
	when Location like '%Nepal%' then 'Nepal'
	when Location like '%Shanghai%' then 'China'
	when Location like '%Chennai%' then 'China'
	when Location like '%Boston%' then 'USA'
	when Location like '%CA%' then 'USA'
	when Location like '%Czech%' then 'Czech Republic'
	when Location like '%Hungary%' then 'Hungary'
	when Location like '%TX%' then 'USA'
	when Location like '%Texas%' then 'USA'
	when Location like '%Delhi%' then 'India'
	when Location like '%Barcelona%' then 'Spain'
	when Location like '%NY%' then 'USA'
	when Location like '%México%' then 'Mexico'
	when Location like '%Saudi%' then 'Saudi Arabia'
	when Location like '%Mumbai%' then 'India'
	when Location like '%Serbia%' then 'Serbia'
	when Location like '%GA%' then 'USA'
	when Location like '%Polska%' then 'Poland'
	when Location like '%Venezuela%' then 'Venezuela'
	when Location like '%New York%' then 'USA'
	when Location like '%OR%' then 'USA'
	when Location like '%Costa Rica%' then 'Costa Rica'
	when Location like '%Hyderabad%' then 'India'
	when Location like '%Europe%' then 'United Kingdom'
	when Location like '%Deutschland%' then 'Germany'
	when Location like '%Lithuania%' then 'Lithuania'
	

    else 'null'
  end as CleanedLocation

  from
    (select Location, count(Location) as Cnt from Users where Location!='' group by Location) q1
) q2
where CleanedLocation!='null'
order by Cnt desc
--) q3

view current version

edited mar 31

created mar 31 jon.doe33427