From: Lee Hachadoorian <lee.hachadoorian@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Aggregating by unique values
Date: Tue, 14 Dec 2010 12:24:17 -0500
Message-ID: <4D07A841.8000109@gmail.com> (raw)
In-Reply-To: <AANLkTinwDzmxMG92_YeG+9+3=J_z1+vwU2DkfspQCia4@mail.gmail.com>
References: <4D06FC75.1050209@gmail.com>
<AANLkTinwDzmxMG92_YeG+9+3=J_z1+vwU2DkfspQCia4@mail.gmail.com>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Perfect. Thanks, --Lee<br>
<br>
On 12/14/2010 03:23 AM, Filip Rembiałkowski wrote:
<blockquote
cite="mid:AANLkTinwDzmxMG92_YeG+9+3=J_z1+vwU2DkfspQCia4@mail.gmail.com"
type="cite">try<br>
<br>
select zip, count(distinct id) from customer_service_date group by
zip;<br>
<br>
<div class="gmail_quote">2010/12/14 Lee Hachadoorian <span
dir="ltr"><<a moz-do-not-send="true"
href="mailto:lee.hachadoorian@gmail.com">lee.hachadoorian@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt
0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left: 1ex;">Hello,<br>
<br>
I'm trying to count customers who have received services by
ZIP code,<br>
but I want to count each customer only once even though
customers may<br>
have received services on multiple dates, and therefore appear
in the<br>
table multiple times. There *is* a separate customers table,
but because<br>
of dirty data, I cannot rely on it.<br>
<br>
The best I can come up with is:<br>
<br>
SELECT<br>
zip, count(*) AS count_serviced<br>
FROM<br>
(SELECT DISTINCT zip, id FROM customer_service_date) a<br>
GROUP BY<br>
zip<br>
;<br>
<br>
The table (with some irrelevant fields dropped) is:<br>
<br>
CREATE TABLE customer_service_date<br>
(<br>
id integer,<br>
address character varying,<br>
city character varying,<br>
state character varying,<br>
zip character varying,<br>
service_date date<br>
)<br>
;<br>
<br>
The table is missing a primary key field, but it would be (id,<br>
service_date) if it had one.<br>
<br>
Any suggestions to improve this?<br>
<br>
Thanks,<br>
--Lee<br>
<br>
--<br>
Lee Hachadoorian<br>
PhD Student, Geography<br>
Program in Earth & Environmental Sciences<br>
CUNY Graduate Center<br>
<font color="#888888"><br>
<br>
--<br>
Sent via pgsql-sql mailing list (<a moz-do-not-send="true"
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a moz-do-not-send="true"
href="http://www.postgresql.org/mailpref/pgsql-sql";
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br;
</font></blockquote>
</div>
<br>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
</pre>
</body>
</html>
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: <4D07A841.8000109@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