CODEWITHSUNDEEP
google-bigquerycommon-table-expression
Oluwasegun Omoboriowo
Oluwasegun Omoboriowo

Reputation: 11

Syntax error: Expected keyword AS but got "("

I have been getting the error message when trying to write the CTE SQL code.

WITH PopulationVancine (Date, Location, population, continent, new_vacinations, RollingPeopleVaccinated) 
AS (
SELECT Death.date,Death.location, Death.population, Death.continent, vacine.new_vaccinations, SUM (vacine.new_vaccinations) OVER (PARTITION BY Death.location order by Death.location, Death.date) AS RollingPeopleVaccinated
    
FROM `my-data-project-96387.PortfolioProjectSamp.CovidDeath` as Death
INNER JOIN `my-data-project-96387.PortfolioProjectSamp.CovidVacination` as vacine
ON Death.location = vacine.location
AND Death.date = vacine.date
WHERE Death.continent is not null
)
Select *
From PopulationVancine

Please I will need your assist as regards this error message.

Upvotes: 1

Views: 2548

Answers (3)

Maria Hoffens
Maria Hoffens

Reputation: 1

I think it is just the version of SQL that we are using and the syntax differs a bit of what Alex on his videos has, but your answers have been extremely helpful and now I am able to finish this part of the project.

Upvotes: 0

JohnyD
JohnyD

Reputation: 11

Emmanuel Korli's answer worked brilliantly. Maybe it's something specific for Big Query or Alex's work, but the query does work without the first

"(Date, Location, population, continent, new_vacinations, RollingPeopleVaccinated)"

In my case it looks like this:

WITH PopVsVac
AS
(
SELECT 
  dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations, 
  SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.location, dea.date) AS rollout_vaccines
FROM
  COVID.covid_deaths AS dea
JOIN
  COVID.covid_vaccinations AS vac
    on dea.location = vac.location
    and dea.date = vac.date
WHERE
  dea.continent is not null
ORDER BY
  2, 3 
)
SELECT
  *, (rollout_vaccines/population)*100
FROM 
  PopVsVac

Upvotes: 1

Emmanuel Korli
Emmanuel Korli

Reputation: 23

Working on the same project and got this error too as opposed to the script. I removed the (Date, Location, population, continent, new_vacinations, RollingPeopleVaccinated) and it worked with same results as script.Try this:

WITH PopulationVancine 
AS (
SELECT Death.date,Death.location, Death.population, Death.continent, vacine.new_vaccinations, SUM (vacine.new_vaccinations) OVER (PARTITION BY Death.location order by Death.location, Death.date) AS RollingPeopleVaccinated
FROM `my-data-project-96387.PortfolioProjectSamp.CovidDeath` as Death
INNER JOIN `my-data-project-96387.PortfolioProjectSamp.CovidVacination` as vacine
ON Death.location = vacine.location
AND Death.date = vacine.date
WHERE Death.continent is not null
)
Select *
From PopulationVancine

Upvotes: 2

Related Questions