CODEWITHSUNDEEP
google-bigquery
David Caesar
David Caesar

Reputation: 11

Google BigQuery wouldn't run when I include 'WHERE' & 'GROUP BY' clause to my query

Each time I run a (Select, From, Limit) query when not aggregating data on Google BigQuery, a table is displayed for my query (it works) but each time I add any other 'Clause' such as 'WHERE' & 'GROUP BY' - an error always gets displayed. For example:

SELECT
  cigarette_use,
  AVG(weight_pounds) baby_weight,
  AVG(mother_age) mother_age,
  STDDEV( weight_pounds) baby_weight_stdev,
FROM
  [publicdata:samples.natality]
LIMIT
  1000
WHERE
  year=2003
  AND state='OH'
GROUP BY
  cigarette_use;

For the query above, this error got displayed -

"Error: Encountered " "WHERE" "WHERE "" at line 10, column 1. Was expecting: <EOF>
Job ID: decent-courage-101120:job_Ts2AJAeI8SijokiKCnV5joh5VQg"

And when I removed the 'WHERE' clause from the query i.e.

WHERE
  year=2003
  AND state='OH'

This error got displayed -

"Error: Encountered " "GROUP" "GROUP "" at line 10, column 1. Was expecting: <EOF>
Job ID: decent-courage-101120:job_Hq_Ux9x-pBGwcwaG7wJ8KlthUys"

Can someone please tell me what I'm doing wrong and what I can do to run simple queries like above on Google BigQuery without encountering errors?

Thank You.

Upvotes: 1

Views: 4062

Answers (1)

Pentium10
Pentium10

Reputation: 208022

You need to use LIMIT at the very end of your query:

SELECT cigarette_use,
       AVG(weight_pounds) baby_weight,
       AVG(mother_age) mother_age,
       STDDEV(weight_pounds) baby_weight_stdev,
FROM [publicdata:samples.natality]
WHERE YEAR=2003
  AND STATE='OH'
GROUP BY cigarette_use
LIMIT 1000;

Upvotes: 5

Related Questions