CODEWITHSUNDEEP
mysqlsqlpostgresqlgroup-byaggregate
YBathia
YBathia

Reputation: 902

cumulative sum of number of records grouped by week

I have the following database schema

ID  creation_date
1      2019-06-03
2      2019-06-04
3      2019-06-04
4      2019-06-10
5      2019-06-11

I need to find out the total size of the table group by week. The output I am looking for is something like

year   week number_of_records
2019    23      3
2019    24      5

I am writing the following query which only gives me number of record created in each week

> select year(creation_date) as year, weekofyear(creation_date) as week,
> count(id) from input group by year, week;

Output I get is

year   week number_of_records
2019    23      3
2019    24      2

Upvotes: 4

Views: 4003

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You seem to want a cumulative sum. You can do this with window functions directly in an aggregation query:

select year(creation_date) as year, weekofyear(creation_date) as week,
       count(*) as number_of_starts,
       sum(count(*)) over (order by min(creation_date)) as number_of_records
from input
group by year, week;

Upvotes: 0

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

Take a look to window (or analytic) functions. Unlike aggregate functions, window functions preserve resulting rows and facilitate operations related to them. When using order by in over clause, windowing is done from first row to current row according to specified order, which is exactly what you need.

select year, week, sum(number_of_records) over (order by year, week)
from (
  select year(creation_date) as year, weekofyear(creation_date) as week,
  count(id) as number_of_records
  from input group by year, week
) your_sql

I guess you will also need to reset sum for each year, which I leave as exercise for you (hint: partition clause).

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

For versions prior to 8.0...

Schema (MySQL v5.7)

CREATE TABLE my_table
(ID SERIAL PRIMARY KEY
,creation_date DATE NOT NULL
);

INSERT INTO my_table VALUES
(1    ,  '2019-06-03'),
(2     , '2019-06-04'),
(3     , '2019-06-04'),
(4      ,'2019-06-10'),
(5      ,'2019-06-11');

Query #1

SELECT a.yearweek
, @i:=@i+a.total running
FROM
(SELECT DATE_FORMAT(x.creation_date,'%x-%v') yearweek
, COUNT(*) total
FROM my_table x
GROUP BY yearweek
)a
JOIN (SELECT @i:=0) vars
ORDER BY a.yearweek;

| yearweek | running |
| -------- | ------- |
| 2019-23  | 3       |
| 2019-24  | 5       |

---

View on DB Fiddle

Upvotes: 0

Related Questions