Re: count(*) - join question

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