Re: ERROR: syntax error at or near "group"

--005045016333664764047bc87210
Content-Type: text/plain; charset=ISO-8859-1

Secondly this is not the right group to address such problems. This is an
sql-syntax related issue. You should address this question to
pgsql-general [at] postgresql.org or better still, pgsql-sql [at] postgresql.org.

Thanks,
Shruthi

On 12/28/09, Shruthi A <shruthi.iisc [at] gmail.com> wrote:
>
> On 12/28/09, Premila Devi <premiladevi.dayalan [at] agbnielsen.com> wrote:
> >
> > select sum(alq.quantity + alq.locked), category.id, alq.assetStatus.name
> >
> > from AssetLocationQuantity alq inner join alq.asset asset inner join
> asset.assetModel model inner join model.category category
> >
> > group by category, alq.assetStatus.name;
> >
> > ERROR: syntax error at or near "group"
> >
>
> I think you've got several parts of the syntax wrong here. Looking at your
> query, we cannot even figure out what are the structures of your tables and
> what is it that you are trying to get out of your query. I am guessing that
> 'AssetLocationQuantity' is a table and 'alq' is an alias for this
> table. This table has columns 'quantity', 'locked' and 'assetStatus'.
> 'Category' is another table, having column 'id'. But what are '
> alq.assetStatus.name', 'alq.asset', 'asset', 'asset.assetModel',
> 'model.category'??? Its very confusing..
>
> First of all, you *cannot* join columns. You have to join 2 tables 'on a
> condition' involving columns of the 2 tables. And you can join only 2
> tables at a time. If you want to join multiple tables, you need to
> 'cascade' the join by first joining a pair, considering the resultant as a
> single table and join this resultant with the third table and so on.
>
> Next, you should give a join condition (for each inner join) before the
> 'group by' clause. The general syntax should be something like this:
>
> select ...
> from T1 inner join T2 on T1.c1 = T2.c2
> inner join T3 on T1.c1 = T3.c3
> group by .....
>
>
> Very importantly, the names appearing in the 'group by' list *cannot* be
> table names. They *have to* be column names.
>
> Next, each name specified in the 'select' and 'group by' clauses must be
> unambiguous. ie if any column name appears in more than one table refered
> to in the query, then the column name should be qualified (prepended) with
> the table name in both the clauses.
>
> And SQL is quite strict about this requirement: All the names appearing in
> the 'group by' clause *should* appear in the 'select' clause also. And
> all the names appearing in the 'select' clause that dont appear in 'group'
> by, *have to* be aggregate functions only. Else you will get a syntax
> error.
>
> We might try to help you write the correct query if you tell us the
> structure of your tables ie all the columns of all the tables. And also
> try to tell us what you want your query to do.
>
> And general tip: Whenever you get a syntax error, look for the error *
> before* the point where the error marker is there.
>
>
> All the best,
> Shruthi
>

--005045016333664764047bc87210
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Secondly this is not the right group to address such problems. =A0This is a=
n sql-syntax related issue. =A0You should address this question to <a href=
=3D"mailto:pgsql-general [at] postgresql.org">pgsql-general [at] postg resql.org</a> o=
r better still, <a href=3D"mailto:pgsql-sql [at] postgresql.org">pgsql-sql [at] post=
gresql.org</a>. 
 Thanks, Shruthi <div>On 12/28/09,=
Shruthi A <<a href=3D"mailto:shruthi.=
iisc [at] gmail.com">shruthi.iisc [at] gmail.com</a>> wrote:<blockquote cla=
ss=3D"gmail_quote" style=3D"margin-top: 0; margin-right: 0; margin-bottom: =
0; margin-left: 0; margin-left: 0.80ex; border-left-color: #cccccc; border-=
left-width: 1px; border-left-style: solid; padding-left: 1ex">
On 12/28/09, Premila Devi <<a href=3D"mailto:premilade=
vi.dayalan [at] agbnielsen.com" target=3D"_blank" onclick=3D"return top.js.OpenE=
xtLink(window,event,this)">premiladevi.dayalan [at] agbnielsen.co m</a>> wrote=
: 
> > select sum(alq.quantity + alq.locked), <a href=3D"http://cate=
gory.id" target=3D"_blank" onclick=3D"return top.js.OpenExtLink(window,even=
t,this)">category.id</a>, <a href=3D"http://alq.assetStatus.name" target=3D=
"_blank" onclick=3D"return top.js.OpenExtLink(window,event,this)">alq.asset=
Status.name</a> 
> > from AssetLocationQuantity alq inner join alq.asset asset in=
ner join asset.assetModel model inner join model.category category > =
 > group by category, <a href=3D"http://alq.assetStatus.name" target=
=3D"_blank" onclick=3D"return top.js.OpenExtLink(window,event,this)">alq.as=
setStatus.name</a>; 
> > ERROR:=A0 syntax error at or near &=
quot;group" > I think you've got several part=
s of the syntax wrong here.=A0=A0Looking at your query, we cannot even figu=
re out what are the structures of your tables and what is it that you are t=
rying to get out of your query.=A0=A0I am guessing that 'AssetLocationQ=
uantity' is a table and 'alq' is an alias for this table.=A0=A0=
This table has columns 'quantity', 'locked' and 'assetS=
tatus'.=A0=A0 'Category' is another table, having column 'i=
d'.=A0=A0 But what are '<a href=3D"http://alq.assetStatus.name" tar=
get=3D"_blank" onclick=3D"return top.js.OpenExtLink(window,event,this)">alq=
..assetStatus.name</a>',=A0=A0'alq.asset', 'asset', '=
;asset.assetModel', 'model.category'???=A0=A0 Its very confusin=
g.. 
 First of all, you cannot join columns.=A0=A0You have to join 2 t=
ables 'on a condition' involving columns of the 2 tables.=A0=A0 And=
you can join only 2 tables at a time.=A0=A0If you want to join multiple ta=
bles, you need to 'cascade' the join by first=A0=A0joining a pair, =
considering the resultant as a single table and join this resultant with th=
e third table and so on. 
 Next, you=A0should give a join=A0condition (for=A0=A0each inner join) b=
efore=A0the 'group by' clause.=A0=A0 The general syntax should be s=
omething like this: select ... from=A0=A0T1 inner join T2=A0=A0on=
T1.c1 =3D T2.c2 
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0inner join=A0=A0T3=A0=A0 on=A0=A0 T1.c1 =3D =
T3.c3 group by ..... Very importantly, the names appearing in=
the 'group by' list cannot be table names.=A0=A0They <st=
rong>have to be column names. 
 Next, each name specified in the 'select' and 'group by'=
; clauses must be unambiguous.=A0=A0ie if any column name appears in more t=
han one table refered to in the query, then the column name should be quali=
fied (prepended) with the table name in both the clauses. 
 And SQL is quite strict about this requirement:=A0=A0All the names appe=
aring in the 'group by' clause should appear in the 'sel=
ect' clause also.=A0=A0 And all the names appearing in the 'select&=
#39; clause that dont appear in 'group' by, have to</str=
ong> be aggregate functions only.=A0=A0Else you will get a syntax error=
.. 
 We might try to help you write the correct query if you tell us the str=
ucture of your tables ie all the columns of all the tables.=A0=A0 And also =
try to tell us what you want your query to do. And general tip: =A0W=
henever you get a syntax error, look for the error before</stron=
g> the point where the error marker is there. 
 All the best, Shruthi </blockquote>=
</div> 

--005045016333664764047bc87210--