0

I am trying to run a query on AWS Athena

SELECT t1.col1,
             t1.col2,
             t2.col2,
             t2.col3
    FROM "db"."table1" AS t1
    INNER JOIN "db2"."table2" AS t2
        ON t1.col2 = t2.col2
    GROUP BY  t1.col1, t2.col3
    HAVING COUNT(1) = 1 LIMIT 10;

but getting below error

SYNTAX_ERROR: line 2:10: '"t1"."col2"' must be an aggregate expression or appear in GROUP BY clause

Is there any way I can disable ONLY_FULL_GROUP_BY in AWS Athena or perform a GROUP BY on partial select columns.

Thanks in advance.

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
rahulb
  • 970
  • 4
  • 12
  • 24
  • 1
    What's your expected result? When you `group by t1.col1, t1.col2, t2.col3` (and remove `t2.col2` from select clause, since it's equal to `t1.col2`), how does the result differ from what you want? – Piotr Findeisen Nov 29 '18 at 20:06

1 Answers1

1

I don't know anything about AWS Athena, but it seems like you may still run into issues even if you disable ONLY_FULL_GROUP_BY (whatever that is).

You are getting a SYNTAX error - because you are not aggregating by t1.col2 and t2.col2. In SQL, you must group by every column that is not part of an aggregate function. See this answer for why: Why do we need GROUP BY with AGGREGATE FUNCTIONS?

Moreover, you may want to reexamine at your query - you are not selecting any aggregate function (count, avg, etc), and yet you are trying to group the results.

banncee
  • 959
  • 14
  • 30