Sure, but the error message is wrong. It should say the following or something similar to avoid confusion:
"Expression must be in Group By Key or Aggregate function: t1.no_null" not "Expression not in Group By Key t1" For example, the error message for this in PostgreSQL is: "ERROR: column "t1.no_null" must appear in the GROUP BY clause or be used in an aggregate function" On Mar 9, 2010, at 3:05 AM, Sonal Goyal wrote: Hi, Let me explain this through an example: Lets assume your table looks like: aux1.objname aux1.no_null ------------------ ---------------- AA 1 AA 2 AB 1 AB 3 When you do a select aux1.objname, aux1.no_null group by objname, you are grouping the AAs and the ABs together. However, you need an aggregate function over no_null so that you can get the value of no_null corresponding to the groups. When you use a group by over a column, other columns that you select either need to be grouped, or aggregated in some form. This is what is missing in your query. HTH. Thanks and Regards, Sonal On Tue, Mar 9, 2010 at 4:20 PM, Jan Stöcker <[email protected]<mailto:[email protected]>> wrote: Hi, I am stuck with what is probably a beginner’s mistake, but I simply don’t know what’s wrong. I have two tables aux1 and aux2, with each two columns objname (STRING) and no_null (INT). I want to find all entries of objname appearing in both tables and gave hive the following statement: SELECT t1.objname, t1.no_null, t2.no_null, (t1.no_null + t2.no_null) AS null_sum FROM aux1 t1 JOIN aux2 t2 ON (t1.objname = t2.objname) GROUP BY t1.objname SORT BY null_sum LIMIT 30; But I got the error message “Error in semantic analysis: line 1:19 Expression Not In Group By Key t1”. I don’t really understand what that means. Anyone can help me? Regards, Jan
