Thread: select tbl1.attr1, number of tuples where tbl2.attr1=tbl1.attr1
Hey, I have three tables. table persons { login, person_id UNIQUE } table views { person_id, timestamp } table partners { person_id UNIQUE, domain } for each tuple in partners i want to know the number of tuples in views with the same person_id. I tried sth like select partners.person_id, count(views.person_id) from partners, views where views.person_id = partners.person_id but it said, partners.person_id must be grouped. Then i tried select partners.person_id, count(views.person_id) from partners, views where views.person_id = partners.person_id group byviews.person_id but this gave very strange results which are definitely not what i wanted. How can I do that? TIA, Markus Bertheau Cenes Data GmbH Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com
Greetings, I've been on this list for just a couple of days now; and I've seen the word 'tuple' here a couple of times. Excuse my lack my 'database'-jargon, but what is a 'tuple'? Cheerio! Kr. Bonne. -- KB905-RIPE Belgacom IP networking (c=be,a=rtt,p=belgacomgroup,s=Bonne,g=Kristoff) Internet, IP and IP/VPN kristoff.bonne@skypro.be Faxbox : +32 2 2435122
"Markus Bertheau" <twanger@eudoramail.com> writes: > Then i tried > select partners.person_id, count(views.person_id) from partners, views where views.person_id = partners.person_id groupby 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
Its a row. http://www.ca.postgresql.org/docs/faq-english.html#4.18 On Fri, 22 Jun 2001, Kristoff Bonne wrote: > Greetings, > > I've been on this list for just a couple of days now; and I've seen the > word 'tuple' here a couple of times. > > Excuse my lack my 'database'-jargon, but what is a 'tuple'? > > Cheerio! Kr. Bonne. >