pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCounting the number of distinct rows returned
5+ messages / 5 participants
[nested] [flat]
* Counting the number of distinct rows returned
@ 1999-08-18 23:59 Drew Whittle <drew@albatross.co.nz>
1999-08-19 05:20 ` Re: [SQL] Counting the number of distinct rows returned tjk@tksoft.com <tjk@tksoft.com>
1999-08-19 13:18 ` Re: [SQL] Counting the number of distinct rows returned Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Drew Whittle @ 1999-08-18 23:59 UTC (permalink / raw)
To: pgsql-sql
How can I return the number of records (ie a count(user_id)) when using a
statement like this:
select distinct user_id from sales_by_region;
I have tried things like:
select count(distinct user_id) from sales_by_region (parse error)
select distinct count(user_id) from sales_by_region (useless because count
is performed before the distinct)
HELP!
Thanks,
Drew
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Counting the number of distinct rows returned
1999-08-18 23:59 Counting the number of distinct rows returned Drew Whittle <drew@albatross.co.nz>
@ 1999-08-19 05:20 ` tjk@tksoft.com <tjk@tksoft.com>
1 sibling, 0 replies; 5+ messages in thread
From: tjk@tksoft.com @ 1999-08-19 05:20 UTC (permalink / raw)
To: drew@albatross.co.nz; +Cc: pgsql-sql
Drew,
Not really the answer you were asking, but in general, your
program will probably be using some interface other than
psql, i.e. libpq or Pg.pm, or whatever.
E.g. in C, if your PgQuery struct is pg1, then you
would do your query and retrieve the number of records
found with "pg1->PQntuples(pg1->reply)"
Troy
>
> How can I return the number of records (ie a count(user_id)) when using a
> statement like this:
>
> select distinct user_id from sales_by_region;
>
> I have tried things like:
>
> select count(distinct user_id) from sales_by_region (parse error)
> select distinct count(user_id) from sales_by_region (useless because count
> is performed before the distinct)
>
> HELP!
>
> Thanks,
>
> Drew
>
>
>
> ************
> Check out "PostgreSQL Wearables" @ http://www.pgsql.com
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Counting the number of distinct rows returned
1999-08-18 23:59 Counting the number of distinct rows returned Drew Whittle <drew@albatross.co.nz>
@ 1999-08-19 13:18 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 1999-08-19 13:18 UTC (permalink / raw)
To: Drew Whittle <drew@albatross.co.nz>; +Cc: pgsql-sql
Drew Whittle <drew@albatross.co.nz> writes:
> How can I return the number of records (ie a count(user_id)) when using a
> statement like this:
> select distinct user_id from sales_by_region;
count() with a GROUP BY will do what you want, I think --- see other
thread with Doug Younger for an example.
> I have tried things like:
> select count(distinct user_id) from sales_by_region (parse error)
Unfortunately we don't handle count(distinct ...) --- we should, since
it's in SQL92.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Counting the number of distinct rows returned
@ 1999-08-19 13:56 Sejin Oh <soh@cyberix.com>
1999-08-22 12:44 ` Re: [SQL] Counting the number of distinct rows returned Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 5+ messages in thread
From: Sejin Oh @ 1999-08-19 13:56 UTC (permalink / raw)
To: Drew Whittle <drew@albatross.co.nz>; +Cc: pgsql-sql
yeah... GROUP BY should do..
try this:
SELECT COUNT(*) FROM sales_by_region GROUP BY user_id;
Regards,
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Drew Whittle <drew@albatross.co.nz>
Cc: pgsql-sql@postgreSQL.org <pgsql-sql@postgreSQL.org>
Date: Thursday, August 19, 1999 9:35 AM
Subject: Re: [SQL] Counting the number of distinct rows returned
>Drew Whittle <drew@albatross.co.nz> writes:
>> How can I return the number of records (ie a count(user_id)) when using a
>> statement like this:
>
>> select distinct user_id from sales_by_region;
>
>count() with a GROUP BY will do what you want, I think --- see other
>thread with Doug Younger for an example.
>
>> I have tried things like:
>> select count(distinct user_id) from sales_by_region (parse error)
>
>Unfortunately we don't handle count(distinct ...) --- we should, since
>it's in SQL92.
>
> regards, tom lane
>
>************
>Check out "PostgreSQL Wearables" @ http://www.pgsql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Counting the number of distinct rows returned
1999-08-19 13:56 Re: [SQL] Counting the number of distinct rows returned Sejin Oh <soh@cyberix.com>
@ 1999-08-22 12:44 ` Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 5+ messages in thread
From: Herouth Maoz @ 1999-08-22 12:44 UTC (permalink / raw)
To: Sejin Oh <soh@cyberix.com>; Drew Whittle <drew@albatross.co.nz>; +Cc: pgsql-sql
At 16:56 +0300 on 19/08/1999, Sejin Oh wrote:
> yeah... GROUP BY should do..
>
> try this:
>
> SELECT COUNT(*) FROM sales_by_region GROUP BY user_id;
No, no, it returns the exact opposite of what he wanted. Imagine a table
that has the values
fld
===
2
1
2
1
3
Doing select distinct on this table would return
fld
===
2
1
3
Therefore the number Drew needed is 3. But SELECT COUNT(*) with GROUP BY
would give:
fld count
=== =====
2 2
1 2
3 1
That is, for each distinct value, it will give the frequency of that value
in the table. This is not the number of distinct values, it is the number
of times each distinct value appears.
I have once already posted a (kinda kludgy) solution for this in the
absence of COUNT DISTINCT in PostgreSQL:
SELECT count(*)
FROM the_table t1
WHERE int4( oid ) =
( SELECT max( int4(oid) )
FROM the_table t2
WHERE t1.fld = t2.fld );
The inner select gives you the maximal oid of the row that has a given
field value. The outer select selects only rows whose oid is this maximal
one. Thus it selects only one row for each distinct value, and so the count
is COUNT DISTINCT. If the original SELECT DISTINCT selected several fields,
more fields should be added to the WHERE clause of the subselect.
A more efficient solution would probably be:
SELECT DISTINCT( fld )
INTO TABLE tmp_tbl
FROM the_table;
SELECT COUNT( * ) -- This is the query that returns the needed result
FROM tmp_tbl;
DROP TABLE tmp_tbl;
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-08-22 12:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-18 23:59 Counting the number of distinct rows returned Drew Whittle <drew@albatross.co.nz>
1999-08-19 05:20 ` tjk@tksoft.com <tjk@tksoft.com>
1999-08-19 13:18 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-19 13:56 Re: [SQL] Counting the number of distinct rows returned Sejin Oh <soh@cyberix.com>
1999-08-22 12:44 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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