pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDISTINCT count(*), possible?
8+ messages / 7 participants
[nested] [flat]
* DISTINCT count(*), possible?
@ 1999-06-16 02:59 Matthew Hagerty <matthew@venux.net>
0 siblings, 1 reply; 8+ messages in thread
From: Matthew Hagerty @ 1999-06-16 02:59 UTC (permalink / raw)
To: pgsql-sql
Greetings,
Can someone tell me how I can get this to work?
select DISTINCT count(address1) from aro;
returns 240, which is wrong. But this:
select DISTINCT address1 from aro;
returns 219, which is exactally what I am looking for, minus the query
output. I don't need the tuples, just a count of distinct addresses.
Thanks,
Matthew
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] DISTINCT count(*), possible?
@ 1999-06-16 04:03 Chris Bitmead <chris@tech.com.au>
parent: Matthew Hagerty <matthew@venux.net>
0 siblings, 0 replies; 8+ messages in thread
From: Chris Bitmead @ 1999-06-16 04:03 UTC (permalink / raw)
To: pgsql-sql
I can do it as two queries, but I don't know how to do it as one.
select count(url) as foo into temp2 from urllink group by url;
select count(foo) from temp2;
Matthew Hagerty wrote:
>
> Greetings,
>
> Can someone tell me how I can get this to work?
>
> select DISTINCT count(address1) from aro;
>
> returns 240, which is wrong. But this:
>
> select DISTINCT address1 from aro;
>
> returns 219, which is exactally what I am looking for, minus the query
> output. I don't need the tuples, just a count of distinct addresses.
>
> Thanks,
> Matthew
--
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] DISTINCT count(*), possible?
@ 1999-06-16 08:55 Stuart Rison <stuart@ludwig.ucl.ac.uk>
0 siblings, 4 replies; 8+ messages in thread
From: Stuart Rison @ 1999-06-16 08:55 UTC (permalink / raw)
To: pgsql-sql; matthew@venux.net
Postgres is behaving as expected...
>Can someone tell me how I can get this to work?
>
>select DISTINCT count(address1) from aro;
>
>returns 240, which is wrong.
It's not the answer you were expecting but it's not wrong. The DISTINCT
means that, of all the row returned by your query, only one instance of
multiple identical tuples (rows) will be returned.
Here your query returns just one row (a count of the total number of rows
in aro), which by definition is unique, and so DISTINCT is redundant.
>But this:
>select DISTINCT address1 from aro;
>
>returns 219, which is exactally what I am looking for, minus the query
>output.
Yes, this time the query is returning all the rows in aro and the DISTINCT
is 'removing' all the duplicate addresses so you get a final count of the
unique addresses... but you also get all the returned query output!
>I don't need the tuples, just a count of distinct addresses.
What you want is the standard SQL:
SELECT count(DISTINCT address1) from aro
First find all unique instances of address1 then return just their count.
Saddly, this is not supported by postgreSQL (yet).
A previously posted work-around (search the GENERAL mailing list with
'count', 'DISTINCT' and 'Herouth') adapted to your query is:
SELECT count(*)
FROM aro t1
WHERE int( oid ) = (
SELECT min( int( t2.oid ) )
FROM test t2
WHERE t2.address1 = t1.address1
);
It's inelegant (because of course, count(DISTINCT) is the 'good' way of
doing things) but it works!
HTH,
Stuart.
+-------------------------+--------------------------------------+
| Stuart Rison | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street |
| Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. |
| Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk |
+-------------------------+--------------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] DISTINCT count(*), possible?
@ 1999-06-16 10:32 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
3 siblings, 0 replies; 8+ messages in thread
From: Herouth Maoz @ 1999-06-16 10:32 UTC (permalink / raw)
To: pgsql-sql; matthew@venux.net
At 11:55 +0300 on 16/06/1999, Stuart Rison wrote:
> A previously posted work-around (search the GENERAL mailing list with
> 'count', 'DISTINCT' and 'Herouth') adapted to your query is:
>
> SELECT count(*)
> FROM aro t1
> WHERE int( oid ) = (
> SELECT min( int( t2.oid ) )
> FROM test t2
> WHERE t2.address1 = t1.address1
> );
Since I apparently own the copyright for this solution :-)
The internal FROM should read "FROM aro t2", not "FROM test t2".
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] DISTINCT count(*), possible?
@ 1999-06-16 14:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
3 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 1999-06-16 14:24 UTC (permalink / raw)
To: Stuart Rison <stuart@ludwig.ucl.ac.uk>; +Cc: pgsql-sql; matthew@venux.net
Another way is
SELECT DISTINCT field INTO temp_table FROM mytable;
SELECT count(*) FROM temp_table;
DROP TABLE temp_table;
which is arguably more efficient than the previous solution
for large tables --- it should involve O(n log n) work rather
than O(n^2). For a small table, the overhead of creating and
dropping a table might overshadow the actual work, though.
In 6.5 you can use "INTO TEMP temp_table" and avoid worrying
about having to invent distinct temp table names for concurrent
users of the database.
The SQL-standard "SELECT count(DISTINCT field)" would be even nicer,
of course, but I dunno when someone will get around to it...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] DISTINCT count(*), possible?
@ 1999-06-16 20:08 Rudy Gireyev <rgireyev@cnmnetwork.com>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
3 siblings, 0 replies; 8+ messages in thread
From: Rudy Gireyev @ 1999-06-16 20:08 UTC (permalink / raw)
To: pgsql-sql
Out of curiosity, more than anything else.
Would the group by solve this problem?
Rudy
On 16 Jun 99, at 10:24, Tom Lane wrote:
> Another way is
>
> SELECT DISTINCT field INTO temp_table FROM mytable;
> SELECT count(*) FROM temp_table;
> DROP TABLE temp_table;
>
> which is arguably more efficient than the previous solution
> for large tables --- it should involve O(n log n) work rather
> than O(n^2). For a small table, the overhead of creating and
> dropping a table might overshadow the actual work, though.
>
> In 6.5 you can use "INTO TEMP temp_table" and avoid worrying
> about having to invent distinct temp table names for concurrent
> users of the database.
>
> The SQL-standard "SELECT count(DISTINCT field)" would be even nicer,
> of course, but I dunno when someone will get around to it...
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] DISTINCT count(*), possible?
@ 1999-06-20 11:40 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
3 siblings, 1 reply; 8+ messages in thread
From: Herouth Maoz @ 1999-06-20 11:40 UTC (permalink / raw)
To: rgireyev@cnmnetwork.com; pgsql-sql
At 23:08 +0300 on 16/06/1999, Rudy Gireyev wrote:
>
> Out of curiosity, more than anything else.
> Would the group by solve this problem?
No. What you need is the number of groups, not the number of members in
each group, which is what group by provides.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] DISTINCT count(*), possible?
@ 1999-06-20 12:47 Thomas Good <tomg@admin.nrnet.org>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 8+ messages in thread
From: Thomas Good @ 1999-06-20 12:47 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: pgsql-sql
On Sun, 20 Jun 1999, Herouth Maoz wrote:
> > Out of curiosity, more than anything else.
> > Would the group by solve this problem?
>
> No. What you need is the number of groups, not the number of members in
> each group, which is what group by provides.
>
> Herouth
Good morning Herouth,
I missed the earlier post(s) on this thread (scrambling to meet
a 01 July go-live date) but the subj caught my eye today as just
yesterday I tried to force 6.3.2 to do a select count(distinct id)
and was rebuffed!
Does this work in 6.5.x?
Also - is there a pg way to do an oracle nvl() type assignment of a
char str to a NULL value during a query (e.g., 'unmarried');
Finally, I have a annoyance. My predecessor apparently was unaware or
distrustful of the text data type. Instead he did this:
note_id int4
line_no int4
noteline char(65)
The user enters lines of text, they are numbered and assigned the (same)
note_id which links them to one note. Doing a select they are reassembled
into one note. (I prefer the text data type as I'm not fond of extra
typing... ;-)
Now, I am porting this old PROGRESS db to Postgres and I need to concat
these char strs into a text attribute. First I dumped and reloaded,
changing the attr holding each str from char() to text. Now I am clumsily
trying to use the concat operator to select into the new table.
INSERT INTO assessment (assets, debits)
SELECT asset1 || asset2 AS assets, debit1 || debit2 AS debits
FROM oldtab
WHERE id = xyz AND date = '04-01-1999';
Sometimes it works and mostly it doesn't.
Would you know of a better approach?
Many thanks,
Tom
------- North Richmond Community Mental Health Center -------
Thomas Good MIS Coordinator, Senior DBA
Vital Signs: tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056
/* Member: Computer Professionals For Social Responsibility */
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~1999-06-20 12:47 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-16 02:59 DISTINCT count(*), possible? Matthew Hagerty <matthew@venux.net>
1999-06-16 04:03 ` Chris Bitmead <chris@tech.com.au>
1999-06-16 08:55 Re: [SQL] DISTINCT count(*), possible? Stuart Rison <stuart@ludwig.ucl.ac.uk>
1999-06-16 10:32 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-16 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-06-16 20:08 ` Rudy Gireyev <rgireyev@cnmnetwork.com>
1999-06-20 11:40 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-20 12:47 ` Thomas Good <tomg@admin.nrnet.org>
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