Обсуждение: Re: [SQL] DISTINCT count(*), possible?
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 t2WHERE 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 | +-------------------------+--------------------------------------+
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
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
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 > >
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
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 */