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