pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Counting 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>
  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-19 05:20  tjk@tksoft.com <tjk@tksoft.com>
  parent: Drew Whittle <drew@albatross.co.nz>
  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-19 13:18  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Drew Whittle <drew@albatross.co.nz>
  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>
  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-22 12:44  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Sejin Oh <soh@cyberix.com>
  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