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_datedate
)
;
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