Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 1A8B91337E4F
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Dec 2010 13:31:12 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 03743-04-2 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Dec 2010 17:31:04 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-qy0-f181.google.com (mail-qy0-f181.google.com
	[209.85.216.181])
	by mail.postgresql.org (Postfix) with ESMTP id DE6BE1337C81
	for <pgsql-sql@postgresql.org>; Tue, 14 Dec 2010 13:24:38 -0400 (AST)
Received: by qyk12 with SMTP id 12so993409qyk.19
	for <pgsql-sql@postgresql.org>; Tue, 14 Dec 2010 09:24:38 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from
	:user-agent:mime-version:to:subject:references:in-reply-to
	:x-enigmail-version:content-type:content-transfer-encoding;
	bh=Dm90YqT68jWCVyLVo2fuY31eDuVfXbGyHXjiAqDsY/o=;
	b=mqvzoy4XQ+qhQPhNI34Zi4SyonTXuglVVhr5J+iQCdrDPAYsNMDXrgb4fX/vL0WnJo
	mo/CRgjQfliftmJQ+zfIodj1VMZUdrsiwYZN4VRZgxlzGMq2o/wn9SlesK3SYogw/HN3
	TpJVZ72ojOCrEq0sbJ+0l8qWUHkRX0dpy/iKU=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:x-enigmail-version:content-type
	:content-transfer-encoding;
	b=WilxQc4afBrqiCA7FkEq+UggsQLrEAPcxKIpZVETi2QsZYqrciNtUY8kCOgyoRvOnG
	+WNy1qvXRWj7qGGl3cEknwpNf9DRZZo2eq8Y2xzh+Gg+DdH+O4/o1DPx20x8+CzWY78G
	LK+pv5ReWS3zpTESsqginOFdYgEuZKh7gIRhw=
Received: by 10.224.67.207 with SMTP id s15mr5328037qai.185.1292347478260;
	Tue, 14 Dec 2010 09:24:38 -0800 (PST)
Received: from [146.96.186.170] ([146.96.186.170])
	by mx.google.com with ESMTPS id g32sm105367qck.10.2010.12.14.09.24.35
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Tue, 14 Dec 2010 09:24:36 -0800 (PST)
Message-ID: <4D07A841.8000109@gmail.com>
Date: Tue, 14 Dec 2010 12:24:17 -0500
From: Lee Hachadoorian <lee.hachadoorian@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Aggregating by unique values
References: <4D06FC75.1050209@gmail.com>
	<AANLkTinwDzmxMG92_YeG+9+3=J_z1+vwU2DkfspQCia4@mail.gmail.com>
In-Reply-To: <AANLkTinwDzmxMG92_YeG+9+3=J_z1+vwU2DkfspQCia4@mail.gmail.com>
X-Enigmail-Version: 1.1.2
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.865 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.723,
	RCVD_IN_DNSWL_LOW=-0.7, T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201012/70
X-Sequence-Number: 34906

<!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@mail.gmail.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">&lt;<a moz-do-not-send="true"
            href="mailto:lee.hachadoorian@gmail.com">lee.hachadoorian@gmail.com</a>&gt;</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 &amp; 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@postgresql.org">pgsql-sql@postgresql.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 &amp; Environmental Sciences
CUNY Graduate Center
</pre>
  </body>
</html>