Count owned entries per yearWeek-CodePudding

With this query I get all my owned entries

select
  SUM(quantity) AS sumQuantity
from
  data

Now I would like to know how much I have owned in each week. I have tried, but of course I only get how much I have added/removed each week. Can I somehow read this out with SQL, or do I need some php?

select
  SUM(quantity) AS sumQuantity,
  CONCAT(
    YEAR(entry_date),
    WEEK(entry_date)
  ) AS yearWeek
from
  data
group by
  yearWeek
order by
  yearWeek desc

CodePudding user response：

You current query gives you the total quantity per week of each year. I suspect that you are after a running sum ; in MySQL 8.0, we can this with sum() over() like so:

select
    sum(quantity) AS sumQuantity,
    sum(sum(quantity)) over(order by yearweek(entry_date)) AS runningQuantity
    yearweek(entry_date) AS yearWeek
from data
group by yearWeek
order by yearWeek desc

Side note: you can use built-in fucntion yearweek() for the year/week computation.