postgresqlgroup-bycountingpostgresql-9.2

Count column values which appear in more than one table row with PSQL


I have table like this:

enter image description here

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...


Solution

  • select number, count(*)
    from (
        select number, city
        from t
        group by number, city
    ) s
    group by number
    having count(*) > 1
    order by number