Aggregating by unique values
От | Lee Hachadoorian |
---|---|
Тема | Aggregating by unique values |
Дата | |
Msg-id | 4D06FC75.1050209@gmail.com обсуждение исходный текст |
Ответы |
Re: Aggregating by unique values
|
Список | 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_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
В списке pgsql-sql по дате отправления: