I am running a process on Spark which uses SQL for the most part. In one of the workflows I am getting the following error:

mismatched input 'GROUP' expecting

spark.sql("SELECT state, AVG(gestation_weeks) "
          "FROM natality "
          "WHERE state is not null "
          "HAVING AVG(gestation_weeks) > (SELECT AVG(gestation_weeks) FROM natality) "
          "GROUP BY state").show()

I cannot figure out what the error is for the life of me

I've tried checking for comma errors or unexpected brackets but that doesn't seem to be the issue

CodePudding user response：

The SQL constructs should appear in the following order:

SELECT
FROM 
WHERE 
GROUP BY **
HAVING   **
ORDER BY