0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for spacecraft operators, scientists, engineers, and enthusiasts

with wVirgLocation as (
  select
    upper(reverse(left(reverse(location), charindex(' ,', reverse(location))))) as upperLocation
    , count(*) as countRegister
  from
    users
  where
    location like '%,%'
  and
    location <> ''
  group by
    upper(reverse(left(reverse(location), charindex(' ,', reverse(location)))))
), nVirgLocation as (
  select
    trim(upper(location)) as upperLocation
    , count(*) as countRegister
  from
    users
  where
    location not like '%,%' -- cidade , estado , pais
  and
    location <> ''
  group by
    trim(upper(location))
), joinLocation as (
  select
    nVirgLocation.*
  from
    nVirgLocation
union all
  select
    wVirgLocation.*
  from
    wVirgLocation
), groupLocation as (
  select
    trim(joinLocation.upperLocation) as upperLocation
    , sum(joinLocation.countRegister) as countRegister
  from
    joinLocation
  group by
    trim(joinLocation.upperLocation)
) select
    *
  from
    groupLocation
  order by
    countRegister desc;

fork query

edited jan 22

created jan 22 anonymous