I have table like this:
I need get numbers, wich is repeated in different citys and also get these citys quantity for each number.
That is from this table, I need result:
number | repeated citys quantity
----------
222 | 2
because number 222
is repeated in 2 different city.
I have this solution:
1) greate function which returns unique values from array, for example array_unique()
2) and then:
SELECT number, array_length(uniq_city_list, 1) FROM (
SELECT number, array_unique(array_agg(city)) AS uniq_city_list FROM mytable
GROUP BY number
)
AS t
WHERE array_length(uniq_city_list, 1) > 1
But, may be there is better solution for doing this? I think this is not optimal query...
select number, count(*)
from (
select number, city
from t
group by number, city
) s
group by number
having count(*) > 1
order by number