How to have MySQL return multiple column of values based on a different benchmark?

I have a MySQL database table (sales) consisting of sales data with their dates.

| Sales ID |    Date     | Values |
|---------------------------------|
| 1        | 01/01/2020  |  1,500 |
| 2        | 02/01/2020  |  2,000 |
| 3        | 07/02/2020  |  1,000 |
| 4        | 12/03/2020  |  2,000 |
| 5        | 21/06/2020  |  1,000 |
| 6        | 11/08/2020  |  4,200 |

I have a SQL query that relies on a specific date benchmark.

```
SET @benchmark = "31/01/2020";

SELECT SUM(Values) FROM sales
WHERE (date < @benchmark);
```

This query supposedly returns only one numeric value. Now I set the condition to be dependant on the benchmark. The problem is, I don’t want this benchmark to stay constant. I need the query to return 14 values for each end of month benchmark from January 2020 to February 2021.

So the result would probably be something like this,

|              SUM(Values)            |
|-------------------------------------|
|(result for benchmark = "31/01/2020")|
|(result for benchmark = "29/02/2020")|
|(result for benchmark = "31/03/2020")|
                   ⋮                  
|(result for benchmark = "31/01/2021")|
|(result for benchmark = "28/02/2021")|

Which sets the benchmark for every end of the month. Note that I do not have access to CREATE anything.

I was thinking of a while loop or a temporary table, but those require me to CREATE something which always returns “Error Code: 1142. CREATE command denied to the user ….” or “Error Code: 1044. Access denied for the user ….”.

Thanks for any help or feedback.

Answer

You can list the dates you want and aggregate:

select m.eom, sum(s.sales)
from (select '2020-01-31' as eom union all
      select '2020-02-29' union all
      . . . 
     ) m left join
     sales s
     on s.date < m.eom
group by m.eom;

Note that you can also generate the dates using a recursive CTE or calendar table.

For this particular query, you could also use a cumulative sum:

select last_day(date) as eom, sum(sales),
       sum(sum(sales)) over (order by last_day(date))
from sales
group by eom;

That works for your particular example, but might not work for other logic.