Обсуждение: count(*) - join question
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) |
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