pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (2+ messages)

Message-ID: <20011127142530.C24565@office.vi.net>
Permalink:  ../20011127142530.C24565@office.vi.net/
Also on:    postgresql.org/message-id/20011127142530.C24565@office.vi.net

 · 

reply

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