how to group by a joined query?

From: Weiping He <laser(at)zhengmai(dot)com(dot)cn>
To: pgsql-general(at)postgresql(dot)org
Subject: how to group by a joined query?
Date: 2003-09-01 02:43:33
Message-ID: 3F52B255.7090802@zhengmai.com.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

suppose I've got two table:

laser_uni=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+------+-----------
name | text |
addr | text |

laser_uni=# \d t2
Table "public.t2"
Column | Type | Modifiers
--------+---------+-----------
name | text |
len | integer |
of | integer |

and I want to use join to select out data and then group by one column,
like this:

laser_uni=# select t1.name, t1.addr, t2.name, t2.len, t2.of from t1
right join t2 on t1.name=t2.name group by t2.name;
ERROR: Attribute t1.name must be GROUPed or used in an aggregate function

seems the I must gorup all those fields:

laser_uni=# select t1.name as t1name, t1.addr as t1addr, t2.name as
t2name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group by
t1.name, t1.addr, t2.name, t2.len, t2.of;
t1name | t1addr | t2name | len | of
--------+--------+--------+-----+----
| | henry | 2 | 4
| | laser | 4 | 4
(2 rows)

is it specification compliant or postgresql specific?

Thanks

Laser

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alex 2003-09-01 02:58:19 Re: Quetions on Joins
Previous Message Christopher Browne 2003-09-01 02:06:13 Re: About GPL and proprietary software