Please chek again and inform me.thanx for the information. On 12/28/09, Shruthi A <shruthi.iisc@xxxxxxxxx> wrote: > On 12/28/09, Premila Devi <premiladevi.dayalan@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
