pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jackson, DeJuan <djackson@cpsgroup.com>
To: Dimitri <dimitri@france.Sun.COM>
Cc: pat@patoche.org
Cc: pgsql-sql@postgreSQL.org
Subject: RE: [SQL] problem with join & count
Date: Thu, 4 Feb 1999 16:52:57 -0600
Message-ID: <F10BB1FAF801D111829B0060971D839F64789B@cpsmail> (raw)

djackson=> create table t1 (i SERIAL, n TEXT);
NOTICE:  CREATE TABLE will create implicit sequence t1_i_seq for SERIAL
column t1.i
NOTICE:  CREATE TABLE/UNIQUE will create implicit index t1_i_key for
table t1
CREATE
djackson=> insert into t1(n) VALUES ('toto');   
INSERT 231199 1
djackson=> insert into t1(n) VALUES ('tutu');  
INSERT 231200 1
djackson=> insert into t1(n) VALUES ('titi');  
INSERT 231201 1
djackson=> insert into t1(n) VALUES ('toto');  
INSERT 231202 1
djackson=> create table t2 (i INT, tag TEXT);
CREATE
djackson=> insert into t2(i, tag) VALUES(1, 'alpha');
INSERT 231213 1
djackson=> insert into t2(i, tag) VALUES(1, 'beta');     
INSERT 231214 1
djackson=> insert into t2(i, tag) VALUES(2, 'gamma');     
INSERT 231215 1
djackson=> insert into t2(i, tag) VALUES(3, 'zeta');      
INSERT 231216 1
djackson=> insert into t2(i, tag) VALUES(3, 'epsilon');    
INSERT 231217 1
djackson=> insert into t2(i, tag) VALUES(4, 'omega');        
INSERT 231218 1
djackson=> select * from t1;
i|n   
-+----
1|toto
2|tutu
3|titi
4|toto
(4 rows)

djackson=> select * from t2;
i|tag    
-+-------
1|alpha  
1|beta   
2|gamma  
3|zeta   
3|epsilon
4|omega  
(6 rows)
*****************MY QUERY****************
djackson=> select t1.n, count(t2.tag) from t1, t2 where t1.i=t2.i group
by t1.i, t1.name;
ERROR:  func_get_detail: No such attribute or function 'name'
djackson=> select t1.n, count(t2.tag) from t1, t2 where t1.i=t2.i group
by t1.i, t1.n;   
n   |count
----+-----
toto|    2
tutu|    1
titi|    2
toto|    1
(4 rows)
****************YOUR QUERY AS WRITTEN TO JAN**************
djackson=> select n, count(*) as how_often from t1, t2 where t1.i=t2.i
group by n;
n   |how_often
----+---------
titi|        2
toto|        3
tutu|        1
(3 rows)


Get the point?
	-DEJ
> -----Original Message-----
> From: Dimitri [mailto:dimitri@france.Sun.COM]
> Sent: Thursday, February 04, 1999 4:14 PM
> To: Jackson, DeJuan
> Cc: pat@patoche.org; pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] problem with join & count
> 
> 
> Jackson, DeJuan wrote:
> > 
> > try:
> > SELECT t1.name, count(t2.tag)
> >   FROM t1, t2
> >  WHERE t1.number = t2.number
> >  GROUP BY t1.number, t1.name
> ______________^
> your query will never work :)), you have to miss t1.number...
> 
> Best regards!
> (dim)
> 
> > 
> >         -DEJ
> > 
> > > -----Original Message-----
> > > From: pat@patoche.org [mailto:pat@patoche.org]
> > > Sent: Thursday, February 04, 1999 2:39 PM
> > > To: pgsql-sql@postgreSQL.org
> > > Subject: [SQL] problem with join & count
> > >
> > >
> > > Hi,
> > >
> > > I don't succeed to build an SQL to do the task i describe below.
> > > If someone can helps, thanks in advance.
> > >
> > > I have two tables
> > >
> > > number  | name
> > > --------------
> > >  1      | toto
> > >  2      | titi
> > >  3      | tutu
> > >
> > > (here the number is a primary key)
> > >
> > > and
> > >
> > > number  |  tag
> > > ---------------
> > >  1      | alpha
> > >  1      | beta
> > >  2      | gamma
> > >  3      | zeta
> > >  3      | epsilon
> > >
> > > the number in the left column is the same of the number in
> > > the first table,
> > > but can appear in that second table many times.
> > >
> > > i need an sql query that will return:
> > >
> > > name   |  how_often
> > > -------------------
> > >  toto  | 2
> > >  titi  | 1
> > >  tutu  | 2
> > >
> > > that is, the result table should contain all names present in
> > > the first table
> > > and then the number of times the number associated with the
> > > name appears in
> > > the second table.
> > >
> > > Thanks a lot for your help.
> > >
> > > Patrick
> > >
> > > /\//\/\/\\/\/\//\/\\/\/\\/\\/\//\/\\/\//\/\\/\//\/\\/\//\/\\
> > > Patrick M.   pat@patoche.org    http://www.patoche.org/
> > >
> > >
> 
> -- 
> =====================================================
>  Dimitri KRAVTCHUK  (dim)           Sun Microsystems
>  Benchmark Engineer                 France 
>  dimitri@France.Sun.COM
> =====================================================
> 



view thread (7+ messages)  latest in thread

Message-ID: <F10BB1FAF801D111829B0060971D839F64789B@cpsmail>
Permalink:  ../F10BB1FAF801D111829B0060971D839F64789B@cpsmail/
Also on:    postgresql.org/message-id/F10BB1FAF801D111829B0060971D839F64789B@cpsmail

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: djackson@cpsgroup.com, dimitri@france.Sun.COM, pat@patoche.org
  Subject: Re: [SQL] problem with join & count
  In-Reply-To: <F10BB1FAF801D111829B0060971D839F64789B@cpsmail>
  References: <F10BB1FAF801D111829B0060971D839F64789B@cpsmail>

* 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