try select zip, count(distinct id) from customer_service_date group by zip;
2010/12/14 Lee Hachadoorian <lee.hachadoor...@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
