RE: [SQL] problem with join & count

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(at)france(dot)Sun(dot)COM]
> Sent: Thursday, February 04, 1999 4:14 PM
> To: Jackson, DeJuan
> Cc: pat(at)patoche(dot)org; pgsql-sql(at)postgreSQL(dot)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(at)patoche(dot)org [mailto:pat(at)patoche(dot)org]
> > > Sent: Thursday, February 04, 1999 2:39 PM
> > > To: pgsql-sql(at)postgreSQL(dot)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(at)patoche(dot)org http://www.patoche.org/
> > >
> > >
>
> --
> =====================================================
> Dimitri KRAVTCHUK (dim) Sun Microsystems
> Benchmark Engineer France
> dimitri(at)France(dot)Sun(dot)COM
> =====================================================
>