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

1
votes

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.

1
google-bigquery
You have a low rate. Important on SO, you have to mark accepted answers by using the tick on the left of the posted answer, below the voting. This will increase your rate. See how this works by visinting this link: meta.stackoverflow.com/questions/5234/… - Pentium10

1 Answers

5
votes

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;