Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA02262 for ; Sun, 20 Jun 1999 11:52:14 -0400 (EDT) (envelope-from tomg@admin.nrnet.org) Received: from mail1.panix.com (mail1.panix.com [166.84.0.212]) by trends.net (8.8.8/8.8.8) with ESMTP id IAA14418 for ; Sun, 20 Jun 1999 08:56:24 -0400 (EDT) Received: from mailhost.nrnet.org (root@mailhost.nrnet.org [166.84.192.39]) by mail1.panix.com (8.8.8/8.8.8/PanixM1.3) with ESMTP id IAA02594; Sun, 20 Jun 1999 08:46:18 -0400 (EDT) Received: from admin.nrnet.org (uucp@localhost) by mailhost.nrnet.org (8.8.7/8.8.4) with UUCP id HAA11189; Sun, 20 Jun 1999 07:43:03 -0400 Received: from localhost (tomg@localhost) by admin.nrnet.org (8.8.7/8.8.7) with SMTP id IAA31137; Sun, 20 Jun 1999 08:47:00 -0400 Date: Sun, 20 Jun 1999 08:47:00 -0400 (EDT) From: Thomas Good To: Herouth Maoz cc: "PostgreSQL::SQL List" Subject: Re: [SQL] DISTINCT count(*), possible? In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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 */