Re: count (DISTINCT field) OVER ()

On Nov 9, 2011, at 18:52, Tarlika Elisabeth Schmitz <postgresql6(at)numerixtechnology(dot)de> wrote:

> 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).
>
>
> How can I achieve this?
>
>
> The best I can come up with is:
>
> SELECT id, name,delta, count (*) OVER()
> FROM (
> SELECT DISTINCT ON (id)
> id, name, similarity(name, 'Tooneyvara') as delta
> FROM vtown
> WHERE
> similarity(name, 'Tooneyvara') > 0.1
> ORDER BY id, delta DESC
> ) AS x
> ORDER by delta DESC
>
> --
>
> Best Regards,
> Tarlika Elisabeth Schmitz
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

1. Write your main query in a WITH (CTE)
2. Query #1 with appropriate GROUP BY clause (CTE)
3. In the main statement JOIN 1 and 2

David J.