DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:x-enigmail-version:content-type:content-transfer-encoding;
	b=FtA+B37avTW56EBaVq7/S03s76wXqlVZB7kY16dJs+QJZVqiWjETKKhXCnRpV0+HEO
	uf1gwSYyrVwaSVStQGVnt8VAwYFE8N8CvpERIOlsSRW/8MIoW+SO8+hoIdEZ7Ra+hg1v
	B1gXlEeyFz9H1qQ+9mOtJWcE4ZT5rcHODG42A=
Message-ID: <4D06FC75.1050209@gmail.com>
Date: Tue, 14 Dec 2010 00:11: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: Aggregating by unique values
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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