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).
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).
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
sql - Cumulative total count after each week passes for a given date range - Stack Overflow
sql server 2008 r2 - Cumulative Sum of week sales - Stack Overflow
join - Cumulative total by week - postgresql - Stack Overflow
mysql - Rolling count of total transactions over time - Database Administrators Stack Exchange
What you want is called the cumulative sum, you can do something like:
create table transactions (transactionid int, d date);
insert into transactions (transactionid, d)
values (1, '2014-08-04'),(2,'2014-08-05'), (3, '2014-08-18')
, (4, '2014-08-18'), (5,'2014-08-20');
select x.y, x.w, count(1)
from (
select distinct year(d) as y, week(d) as w
from transactions
) as x
join transactions y
on year(y.d) < x.y
or ( year(y.d) = x.y
and week(y.d) <= x.w )
group by x.y, x.w;
+------+------+----------+
| y | w | count(1) |
+------+------+----------+
| 2014 | 31 | 2 |
| 2014 | 33 | 5 |
+------+------+----------+
I did not see your additional request for 2 2 for 2014. You can do that by replacing:
select distinct year(d) as y, week(d) as w
from transactions
...with an expression that creates the whole domain for weeks. It is often a good idea to create a calendar table that you can use to join against to get reports for missing values etc.
To get the basic data you need an aggregation:
select 1 + floor(datediff(date, mind) / 7) as week,
year(date) as year,
count(*) as num
from atable t cross join
(select min(date) as mind
from atable
) td
group by 1 + floor(datediff(date, mind) / 7),
year(date)
You can extend this using variables to get the cumulative sum:
select week, year, num, (@cum := @cum + num) as cum
from (select 1 + floor(datediff(date, mind) / 7) as week,
year(date) as year,
count(*) as num
from atable t cross join
(select min(date) as mind
from atable
) td
group by 1 + floor(datediff(date, mind) / 7),
year(date)
) x cross join
(select @cum := 0) vars
order by year, week;
I'm a little confused by your query:
- You have a
left joinfromitemstousersas if you expect some items with no valid user id. - You are using
u.idin theselect, but that would beNULLwith no match.
I would suggest:
select it.owner_id,
date_trunc('week', it.created::timestamp) as week_start,
date_trunc('week', it.created::timestamp) + interval '6 day' as week_end,
count(*) as this_week,
sum(count(*)) over (partition by uu.id order by min(timestamp)) as running_count
from items it
group by it.owner_id, week_start;
This uses Postgres syntax because your code looks like Postgres.
Remove user id from the GROUP BY clause and from SELECT list:
select
count(*),
date_trunc('week', CAST(it.created AS timestamp)) as week
from items it
left join users uu on uu.id = item.owner_id
group by week