pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
count(*) - join question
2+ messages / 2 participants
[nested] [flat]

* count(*) - join question
@ 2001-11-26 23:03  Nicole S. Weber <nweber@ic.ac.uk>
  0 siblings, 1 reply; 2+ messages in thread

From: Nicole S. Weber @ 2001-11-26 23:03 UTC (permalink / raw)
  To: pgsql-sql


Hello.

I have set up a database to hold information regarding samples of insects
that I am counting.  I have two tables, one holds information about each
bug (sample number, type, length) and one contains information about each
sample (location, date etc.).  I have included the information on each
below.

I would like to SELECT the total number of bugs per sample, the sample ID,
the sample date and the sample code from the two tables to produce the
output shown here:

sample_id     |  sample_date   |  sample_code    |    count
------------------------------------------------------------
    1         |  05/08/01      |   D15-1         |     5
    2         |  06/08/01      |   D20-1         |     8
    .
    .
    .

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

I am quite new to this and I think I need something more complicated but I
just can't seem to figure it out.

Can anyone help?

Thanks.
Nicole

 Table "animals"
 Attribute  |     Type      |
------------+---------------+
 animal_id  | integer       |
 sample_id  | integer       |
 type_id    | integer       |
 length     | integer       |
 acomments  | character(50) |

Table "samples"
  Attribute    |     Type      |
---------------+---------------+-
 sample_id     | integer       |
 sample_date   | date          |
 sample_code   | character(10) |
 time_id       | integer       |
 net_id        | integer       |
 sample_num    | integer       |
 duration      | numeric(5,2)  |
 count_date    | date          |
 scomments     | character(50) |






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: count(*) - join question
@ 2001-11-27 14:25  Richard Poole <richard.poole@vi.net>
  parent: Nicole S. Weber <nweber@ic.ac.uk>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Poole @ 2001-11-27 14:25 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-11-27 14:25 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-26 23:03 count(*) - join question Nicole S. Weber <nweber@ic.ac.uk>
2001-11-27 14:25 ` Richard Poole <richard.poole@vi.net>

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