Re: [SQL] DISTINCT count(*), possible?

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 */