Re: ERROR: syntax error at or near "group" - Mailing list pgsql-admin

Please chek again and inform me.thanx for the information.

On 12/28/09, Shruthi A <shruthi.iisc@gmail.com> wrote:
> 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
>


--
$älmân