Aggregating by unique values

From: Lee Hachadoorian <lee(dot)hachadoorian(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Aggregating by unique values
Date: 2010-12-14 05:11:17
Message-ID: 4D06FC75.1050209@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jasen Betts 2010-12-14 07:12:31 Re: constraint with check
Previous Message Viktor Bojović 2010-12-13 23:36:57 constraint with check