Hi,
o.k., that makes sense. Actually, now I see that my mistake was the GROUP BY clause, which was not necessary, as the objnames were already unique. Thanks a lot, Jan Von: Sonal Goyal [mailto:[email protected]] Gesendet: Dienstag, 9. März 2010 12:06 An: [email protected] Betreff: Re: error in semantic analysis of join statement 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]> 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
