pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAggregating by unique values
3+ messages / 2 participants
[nested] [flat]
* Aggregating by unique values
@ 2010-12-14 05:11 Lee Hachadoorian <lee.hachadoorian@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Lee Hachadoorian @ 2010-12-14 05:11 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aggregating by unique values
@ 2010-12-14 08:23 Filip Rembiałkowski <filip.rembialkowski@gmail.com>
parent: Lee Hachadoorian <lee.hachadoorian@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Filip Rembiałkowski @ 2010-12-14 08:23 UTC (permalink / raw)
To: Lee Hachadoorian <lee.hachadoorian@gmail.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aggregating by unique values
@ 2010-12-14 17:24 Lee Hachadoorian <lee.hachadoorian@gmail.com>
parent: Filip Rembiałkowski <filip.rembialkowski@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Lee Hachadoorian @ 2010-12-14 17:24 UTC (permalink / raw)
To: pgsql-sql
<!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>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-12-14 17:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-14 05:11 Aggregating by unique values Lee Hachadoorian <lee.hachadoorian@gmail.com>
2010-12-14 08:23 ` Filip Rembiałkowski <filip.rembialkowski@gmail.com>
2010-12-14 17:24 ` Lee Hachadoorian <lee.hachadoorian@gmail.com>
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