From: Lee Hachadoorian <lee.hachadoorian@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Aggregating by unique values
Date: Tue, 14 Dec 2010 00:11:17 -0500
Message-ID: <4D06FC75.1050209@gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lee.hachadoorian@gmail.com
Subject: Re: Aggregating by unique values
In-Reply-To: <4D06FC75.1050209@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox