RE: [SQL] problem with join & count - Mailing list pgsql-sql

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
> =====================================================
>