From: Richard Poole <richard.poole@vi.net>
To: pgsql-sql@postgresql.org
Subject: Re: count(*) - join question
Date: Tue, 27 Nov 2001 14:25:30 +0000
Message-ID: <20011127142530.C24565@office.vi.net> (raw)
In-Reply-To: <20011126221913.P22241-100000@tulip.atm.ox.ac.uk>
References: <20011126221913.P22241-100000@tulip.atm.ox.ac.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard.poole@vi.net
Subject: Re: count(*) - join question
In-Reply-To: <20011127142530.C24565@office.vi.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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