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

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