Re: Aggregating by unique values

try<br /><br />select zip, count(distinct id) from customer_service_date group by zip;<br /><br /><div
class="gmail_quote">2010/12/14Lee Hachadoorian <span dir="ltr"><<a
href="mailto:lee.hachadoorian@gmail.com">lee.hachadoorian@gmail.com</a>></span><br/><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">Hello,<br /><br /> I'm
tryingto count customers who have received services by ZIP code,<br /> but I want to count each customer only once even
thoughcustomers may<br /> have received services on multiple dates, and therefore appear in the<br /> table multiple
times.There *is* a separate customers table, but because<br /> of dirty data, I cannot rely on it.<br /><br /> The best
Ican come up with is:<br /><br /> SELECT<br />    zip, count(*) AS count_serviced<br /> FROM<br />    (SELECT DISTINCT
zip,id FROM customer_service_date) a<br /> GROUP BY<br />    zip<br /> ;<br /><br /> The table (with some irrelevant
fieldsdropped) is:<br /><br /> CREATE TABLE customer_service_date<br /> (<br />  id integer,<br />  address character
varying,<br/>  city character varying,<br />  state character varying,<br />  zip character varying,<br />
 service_datedate<br /> )<br /> ;<br /><br /> The table is missing a primary key field, but it would be (id,<br />
service_date)if it had one.<br /><br /> Any suggestions to improve this?<br /><br /> Thanks,<br /> --Lee<br /><br />
--<br/> Lee Hachadoorian<br /> PhD Student, Geography<br /> Program in Earth & Environmental Sciences<br /> CUNY
GraduateCenter<br /><font color="#888888"><br /><br /> --<br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/>