Re: Aggregating by unique values

Perfect. Thanks, --Lee

On 12/14/2010 03:23 AM, Filip Rembiałkowski wrote:

try

select zip, count(distinct id) from customer_service_date group by zip;

2010/12/14 Lee Hachadoorian <lee.hachadoorian@gmail.com>

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


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center