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

On 12/28/09, Premila Devi <premiladevi.dayalan@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