How to use MAX in CTE using SQL-CodePudding

I'm attempting to use a CTE and a MAX function to find the highest number in a grouping of countries. Ultimately in this table trying to find the total number of vaccinations for each country listed by summing the "new vaccines" . Here's my code so far:

WITH VacGDP (Location, gdp_per_capita, Total_Shots)
as
(
SELECT Location, CovidVaccinations.gdp_per_capita, SUM(cast(New_vaccinations as bigint)) as Total_Shots
FROM CovidVaccinations
WHERE Continent is not null
GROUP BY Location, gdp_per_capita, New_vaccinations
)
SELECT Location, MAX(Total_Shots) as Total_Shots
FROM VacGDP
GROUP BY Location, Total_Shots
ORDER BY Location, Total_Shots DESC

This gives the following table: |Location|Total_Shots| |:----|:-----:| |Afghanistan|6868| |Afghanistan|4015| |Afghanistan|2859| |Afghanistan|NULL|

I'm ultimately trying to just have one row showing Afghanistan 6868, so one row per country in this database.

I'm sure this is something super easy to fix but I'm a bit lost!

CodePudding user response：

Remove New_Vaccinations from your CTE group by
Remove total_shots from your group and order by.

Also you mention you want to see per day but there are no date filters/indicators in your code?