Received: from athena.office.vi.net ([194.88.88.225]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAREQ7m95480 for ; Tue, 27 Nov 2001 09:26:07 -0500 (EST) (envelope-from rp@office.vi.net) Received: from rp by athena.office.vi.net with local (Exim 3.12 #1 (Debian)) id 168jAw-00085g-00; Tue, 27 Nov 2001 14:25:30 +0000 Date: Tue, 27 Nov 2001 14:25:30 +0000 From: Richard Poole To: pgsql-sql@postgresql.org Subject: Re: count(*) - join question Message-ID: <20011127142530.C24565@office.vi.net> References: <20011126221913.P22241-100000@tulip.atm.ox.ac.uk> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <20011126221913.P22241-100000@tulip.atm.ox.ac.uk>; from nweber@ic.ac.uk on Mon, Nov 26, 2001 at 11:03:56PM +0000 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id fAREXgn95725 X-Archive-Number: 200111/367 X-Sequence-Number: 5749 On Mon, Nov 26, 2001 at 11:03:56PM +0000, Nicole S. Weber wrote: > > I have tried to use a join on the sample_id in combination with > COUNT(*)/GROUP BY sample_id: > > SELECT animals.sample_id, samples.sample_date, samples.sample_code, > count(*) > FROM animals, samples > WHERE samples.sample_id = animals.sample_id > GROUP BY animals.sample_id; > > Alas, this returns: > > ERROR: Attribute samples.sample_Dadate must be GROUPed or used in an > aggregate function You need to GROUP BY all the columns that you're not counting the different occurrences of: SELECT animals.sample_id, samples.sample_date, samples.sample_code, count(*) FROM animals, samples WHERE samples.sample_id = animals.sample_id GROUP BY animals.sample_id, samples.sample_date, samples.sample_code; Richard