Mysql Sum over partition by-CodePudding

Hi I am doing MySQL and using 'Sum over (partition by )'

I want to see the values are adding up by following lines like below

but my result is like just

I'm using the following query:

select dea.location, sum(cast(vac.new_vaccinations as signed)) over (partition by dea.location order by dea.location)
From pr.CovidDeaths_csv dea
join pr.CovidVaccinations_csv vac
    on dea.location = vac.location
    and dea.date = vac.date
    where dea.continent is not null
    order by 2;

Does anyone know about this problem?

CodePudding user response：

You're missing the frame specification for window functions in MySQL. It allows you to apply a cumulative sum instead of a static sum:

select dea.location, 
       sum(cast(vac.new_vaccinations as signed)) 
           over(partition by dea.location 
                order by dea.location     ROWS UNBOUNDED PRECEDING)
From pr.CovidDeaths_csv dea
join pr.CovidVaccinations_csv vac
    on dea.location = vac.location
    and dea.date = vac.date
    where dea.continent is not null
    order by 2;

As you've not shared your data from all your tables, I cannot replicate your case, but you can see an analogous pattern on sample data here.

CodePudding user response：

partition by dea.location order by dea.location ? - don't know how mysql will resolve this but partition by dea.location order by dea.date would make sense.