<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Perfect. Thanks, --Lee<br>
<br>
On 12/14/2010 03:23 AM, Filip Rembiałkowski wrote:
<blockquote
cite="mid:AANLkTinwDzmxMG92_YeG+9+3=J_z1+vwU2DkfspQCia4(at)mail(dot)gmail(dot)com"
type="cite">try<br>
<br>
select zip, count(distinct id) from customer_service_date group by
zip;<br>
<br>
<div class="gmail_quote">2010/12/14 Lee Hachadoorian <span
dir="ltr"><<a moz-do-not-send="true"
href="mailto:lee(dot)hachadoorian(at)gmail(dot)com">lee(dot)hachadoorian(at)gmail(dot)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 trying to count customers who have received services by
ZIP code,<br>
but I want to count each customer only once even though
customers 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 I can 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 fields dropped) 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_date date<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 Graduate Center<br>
<font color="#888888"><br>
<br>
--<br>
Sent via pgsql-sql mailing list (<a moz-do-not-send="true"
href="mailto:pgsql-sql(at)postgresql(dot)org">pgsql-sql(at)postgresql(dot)org</a>)<br>
To make changes to your subscription:<br>
<a moz-do-not-send="true"
href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br>
</font></blockquote>
</div>
<br>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
</pre>
</body>
</html>