Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cumulative column values from dates current and previous

I have a table of registrations, around 300K records. I need a SQL statement that will show the total number of registrations for that particular day?

select
count('x'),CONVERT(varchar(12),date_created,111)
from reg group by
cONVERT(varchar(12),date_created,111)
order by
CONVERT(varchar(12),date_created,111)

Result of this query:

169      2011/03/24
3016     2011/03/25
2999     2011/03/26

Desired outcome:

 2011/03/25  3016+169
 2011/03/26  2999+3016+169

How can this be done?

like image 907
Alessandro Avatar asked Mar 27 '11 14:03

Alessandro


People also ask

How do you calculate cumulative values in Excel?

Create a running total formula. In our sample Excel workbook, let's say you want a cumulative total posted in column C. In cell C1, you would type =SUM($B$2:B2). This creates the necessary relative reference point (B2) and absolute reference point ($B$2) for your running tally.

How do you calculate cumulative value?

Cumulative means "how much so far". Think of the word "accumulate" which means to gather together. To have cumulative totals, just add up the values as you go.

How do you calculate cumulative column in SQL?

In SQL server also you can calculate cumulative sum by using sum function. We can use same table as sample table. select dept_no Department_no, count(empno) Employee_Per_Dept, sum(count(*)) over (order by deptno) Cumulative_Total from [DBO].

Is there a cumulative function in Excel?

How to calculate running total (cumulative sum) in Excel. To calculate a running total in Excel, you can use the SUM function combined with a clever use of absolute and relative cells references. In your running total formula, the first reference should always be an absolute reference with the $ sign ($B$2).


1 Answers

Here is two versions to do this. I have tested with 100000 rows spread over 6000 days on a really slow computer with not enough memory, and that shows that the cte version is faster than the loop version. The other versions suggested here (so far) is a lot slower, provided that I have understood the problem correctly.

Recursive CTE (10 seconds)

-- Table variable to hold count for each day
declare @DateCount table(d int, c int, rn int)
insert into @DateCount
  select 
    datediff(d, 0, date_created) as d,
    count(*) as c,
    row_number() over(order by datediff(d, 0, date_created)) as rn
  from reg
  group by datediff(d, 0, date_created)

-- Recursive cte using @DateCount to calculate the running sum
;with DateSum as
(
  select 
    d, c, rn
  from @DateCount
  where rn = 1
  union all
  select 
    dc.d, ds.c+dc.c as c, dc.rn
  from DateSum as ds
    inner join @DateCount as dc
      on ds.rn+1 = dc.rn  
)
select
  dateadd(d, d, 0) as date_created,
  c as total_num
from DateSum
option (maxrecursion 0)

Loop (14 seconds)

-- Table variable to hold count for each day
declare @DateCount table(d int, c int, rn int, cr int)
insert into @DateCount
  select 
    datediff(d, 0, date_created) as d,
    count(*) as c,
    row_number() over(order by datediff(d, 0, date_created)) as rn,
    0
  from reg
  group by datediff(d, 0, date_created)

declare @rn int = 1

-- Update cr with running sum
update dc set
  cr = dc.c  
from @DateCount as dc
where rn = @rn

while @@rowcount = 1
begin
  set @rn = @rn + 1

  update dc set
    cr = dc.c + (select cr from @DateCount where rn = @rn - 1)  
  from @DateCount as dc
  where rn = @rn
end

-- Get the result
select
  dateadd(d, d, 0) as date_created,
  cr as total_num
from @DateCount

Edit 1 The really fast version

The quirky update

-- Table variable to hold count for each day
declare @DateCount table(d int primary key, c int, cr int)
insert into @DateCount
  select 
    datediff(d, 0, date_created) as d,
    count(*) as c,
    0
  from reg
  group by datediff(d, 0, date_created)

declare @rt int = 0
declare @anchor int

update @DateCount set
  @rt = cr = @rt + c,
  @anchor = d
option (maxdop 1)

-- Get the result
select
  dateadd(d, d, 0) as date_created,
  cr as total_num
from @DateCount                
order by d
like image 127
Mikael Eriksson Avatar answered Nov 03 '22 05:11

Mikael Eriksson