Re: count (DISTINCT field) OVER () - Mailing list pgsql-general

Tarlika Elisabeth Schmitz, 10.11.2011 00:52:
> I would like to implement the equivalent of "count (DISTINCT field) OVER ()":
>
>
> SELECT
>    id, name, similarity(name, 'Tooneyvara') as delta,
>    count (id) OVER() AS cnt
>    FROM vtown
>    WHERE
>        similarity(name, 'Tooneyvara')>  0.1
>    ORDER BY  delta DESC
>
> produces result:
> 1787    Toomyvara    0.5    4
> 1787    Toomevara    0.4    4
> 1700    Ardcroney    0.105    4
> 1788    Townsfield    0.1    4
>
> What I would like is a "3" in the cnt column (ignoring id duplicates).
>
>

This should do it:

SELECT id,
        name,
        delta,
        sum(case when rn = 1 then rn else null end)  over() as distinct_id_count
FROM (
     SELECT
       id, name, similarity(name, 'Tooneyvara') as delta,
       row_number() OVER(partition by id) AS rn
       FROM vtown
       WHERE
           similarity(name, 'Tooneyvara') > 0.1
) t
ORDER BY delta DESC