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

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(dot)org or better still, pgsql-sql(at)postgresql(dot)org(dot)

Thanks,
Shruthi

On 12/28/09, Shruthi A <shruthi(dot)iisc(at)gmail(dot)com> wrote:
>
> On 12/28/09, Premila Devi <premiladevi(dot)dayalan(at)agbnielsen(dot)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
>