pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcounting distinct values
5+ messages / 3 participants
[nested] [flat]
* counting distinct values
@ 2000-06-07 23:52 Joseph Shraibman <jks@selectacast.net>
2000-06-08 00:15 ` Re: counting distinct values Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Joseph Shraibman @ 2000-06-07 23:52 UTC (permalink / raw)
To: pgsql-sql
Using the example from
http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I
do if I wanted to know the number of different cities where I had a
friend in each state? select count(city) group by state; would not work
because if you had two friends in the same city it would be counted
twice.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: counting distinct values
2000-06-07 23:52 counting distinct values Joseph Shraibman <jks@selectacast.net>
@ 2000-06-08 00:15 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-06-08 01:04 ` Re: counting distinct values Joseph Shraibman <jks@selectacast.net>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2000-06-08 00:15 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
> Using the example from
> http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I
> do if I wanted to know the number of different cities where I had a
> friend in each state? select count(city) group by state; would not work
> because if you had two friends in the same city it would be counted
> twice.
>
How about?
select city, state, count(*) group by state, city;
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: counting distinct values
2000-06-07 23:52 counting distinct values Joseph Shraibman <jks@selectacast.net>
2000-06-08 00:15 ` Re: counting distinct values Bruce Momjian <pgman@candle.pha.pa.us>
@ 2000-06-08 01:04 ` Joseph Shraibman <jks@selectacast.net>
2000-06-08 02:46 ` Re: counting distinct values Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Joseph Shraibman @ 2000-06-08 01:04 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql
Bruce Momjian wrote:
>
> > Using the example from
> > http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I
> > do if I wanted to know the number of different cities where I had a
> > friend in each state? select count(city) group by state; would not work
> > because if you had two friends in the same city it would be counted
> > twice.
> >
>
> How about?
>
> select city, state, count(*) group by state, city;
OK that doesn't work for me because I want to use other agregates on the
state level.
Forgetting the state/city example.
playpen=> create table tablem ( a int, b int, n int);
CREATE
playpen=> insert into tablem (a, b, n) values (1, 2, 1);
INSERT 35197 1
playpen=> insert into tablem (a, b, n) values (1, 2, 2);
INSERT 35198 1
playpen=> insert into tablem (a, b, n) values (1, 3, 1);
INSERT 35199 1
playpen=> insert into tablem (a, b, n) values (1, 4, 3);
INSERT 35296 1
playpen=> insert into tablem (a, b, n) values (1, 1, 4);
INSERT 35297 1
playpen=> insert into tablem (a, b, n) values (2, 3, 3);
INSERT 35298 1
playpen=> insert into tablem (a, b, n) values (2, 5, 7);
INSERT 35299 1
playpen=> insert into tablem (a, b, n) values (2, 3, 3);
INSERT 35300 1
playpen=> insert into tablem (a, b, n) values (2, 3, 1);
INSERT 35301 1
playpen=> select a, count(*), avg(n), sum(n) from tablem group by a;
a|count|avg|sum
-+-----+---+---
1| 5| 2| 11
2| 4| 3| 14
(2 rows)
... now suppose I want to have the number of distictive b's in the
results as well. I try:
playpen=> select a, count(m.oid), avg(m.n), sum(m.n), (select
count(mm.oid) from tablem mm where m.a = mm.a group by mm.b ) from
tablem m group by a;
ERROR: More than one tuple returned by a subselect used as an
expression.
playpen=>
... even though the subselect should only return one tuple.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: counting distinct values
2000-06-07 23:52 counting distinct values Joseph Shraibman <jks@selectacast.net>
2000-06-08 00:15 ` Re: counting distinct values Bruce Momjian <pgman@candle.pha.pa.us>
2000-06-08 01:04 ` Re: counting distinct values Joseph Shraibman <jks@selectacast.net>
@ 2000-06-08 02:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-06-08 03:09 ` Re: counting distinct values Joseph Shraibman <jks@selectacast.net>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2000-06-08 02:46 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql
Joseph Shraibman <jks@selectacast.net> writes:
>>>> Using the example from
>>>> http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I
>>>> do if I wanted to know the number of different cities where I had a
>>>> friend in each state? select count(city) group by state; would not work
>>>> because if you had two friends in the same city it would be counted
>>>> twice.
Er, what's wrong with select count(distinct city) group by state?
> ... now suppose I want to have the number of distictive b's in the
> results as well. I try:
> playpen=> select a, count(m.oid), avg(m.n), sum(m.n), (select
> count(mm.oid) from tablem mm where m.a = mm.a group by mm.b ) from
> tablem m group by a;
> ERROR: More than one tuple returned by a subselect used as an
> expression.
> playpen=>
> ... even though the subselect should only return one tuple.
Not unless there's only one b value for any one a value --- otherwise
the sub-select will return one row per b group. The error message looks
correct to me.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: counting distinct values
2000-06-07 23:52 counting distinct values Joseph Shraibman <jks@selectacast.net>
2000-06-08 00:15 ` Re: counting distinct values Bruce Momjian <pgman@candle.pha.pa.us>
2000-06-08 01:04 ` Re: counting distinct values Joseph Shraibman <jks@selectacast.net>
2000-06-08 02:46 ` Re: counting distinct values Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-06-08 03:09 ` Joseph Shraibman <jks@selectacast.net>
0 siblings, 0 replies; 5+ messages in thread
From: Joseph Shraibman @ 2000-06-08 03:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql
Tom Lane wrote:
>
> Joseph Shraibman <jks@selectacast.net> writes:
> >>>> Using the example from
> >>>> http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I
> >>>> do if I wanted to know the number of different cities where I had a
> >>>> friend in each state? select count(city) group by state; would not work
> >>>> because if you had two friends in the same city it would be counted
> >>>> twice.
>
> Er, what's wrong with select count(distinct city) group by state?
I thought I tried that, but it didn't work the first time. <shrug> Guess
I mistyped something. Sorry.
>
> > ... now suppose I want to have the number of distictive b's in the
> > results as well. I try:
>
> > playpen=> select a, count(m.oid), avg(m.n), sum(m.n), (select
> > count(mm.oid) from tablem mm where m.a = mm.a group by mm.b ) from
> > tablem m group by a;
> > ERROR: More than one tuple returned by a subselect used as an
> > expression.
> > playpen=>
>
> > ... even though the subselect should only return one tuple.
>
> Not unless there's only one b value for any one a value --- otherwise
> the sub-select will return one row per b group. The error message looks
> correct to me.
>
OK I thought that group by was eleminating duplicates which would then
be counted. That evolved from q simple select(*) ... group by that
worked. Don't know where I screwed it up.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-06-08 03:09 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-07 23:52 counting distinct values Joseph Shraibman <jks@selectacast.net>
2000-06-08 00:15 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-06-08 01:04 ` Joseph Shraibman <jks@selectacast.net>
2000-06-08 02:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-06-08 03:09 ` Joseph Shraibman <jks@selectacast.net>
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