Re: Aggregating by unique values

try

select zip, count(distinct id) from customer_service_date group by zip;

2010/12/14 Lee Hachadoorian <lee(dot)hachadoorian(at)gmail(dot)com>

> Hello,
>
> I'm trying to count customers who have received services by ZIP code,
> but I want to count each customer only once even though customers may
> have received services on multiple dates, and therefore appear in the
> table multiple times. There *is* a separate customers table, but because
> of dirty data, I cannot rely on it.
>
> The best I can come up with is:
>
> SELECT
> zip, count(*) AS count_serviced
> FROM
> (SELECT DISTINCT zip, id FROM customer_service_date) a
> GROUP BY
> zip
> ;
>
> The table (with some irrelevant fields dropped) is:
>
> CREATE TABLE customer_service_date
> (
> id integer,
> address character varying,
> city character varying,
> state character varying,
> zip character varying,
> service_date date
> )
> ;
>
> The table is missing a primary key field, but it would be (id,
> service_date) if it had one.
>
> Any suggestions to improve this?
>
> Thanks,
> --Lee
>
> --
> Lee Hachadoorian
> PhD Student, Geography
> Program in Earth & Environmental Sciences
> CUNY Graduate Center
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>