Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5MDsba88876 for ; Fri, 22 Jun 2001 09:54:37 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f5MDsR303363; Fri, 22 Jun 2001 09:54:31 -0400 (EDT) To: twanger@eudoramail.com cc: pgsql-sql@postgresql.org, bertheau@bab24.de Subject: Re: select tbl1.attr1, number of tuples where tbl2.attr1=tbl1.attr1 In-reply-to: References: Comments: In-reply-to "Markus Bertheau" message dated "Fri, 22 Jun 2001 13:06:32 +0100" Date: Fri, 22 Jun 2001 09:54:27 -0400 Message-ID: <3360.993218067@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/367 X-Sequence-Number: 3657 "Markus Bertheau" writes: > Then i tried > select partners.person_id, count(views.person_id) from partners, views where views.person_id = partners.person_id group by views.person_id > but this gave very strange results which are definitely not what i wanted. I think you mistyped, because that query will also fail: regression=# create table views (person_id int, timestamp timestamp); CREATE regression=# create table partners (person_id int UNIQUE, domain text); NOTICE: CREATE TABLE/UNIQUE will create implicit index 'partners_person_id_key' for table 'partners' CREATE regression=# select partners.person_id, count(views.person_id) regression-# from partners, views where views.person_id = partners.person_id regression-# group by views.person_id; ERROR: Attribute partners.person_id must be GROUPed or used in an aggregate function How about showing us what you *really* did? regards, tom lane