Postgresql count rows per date adding up

Question

I'm trying to make a diagram where I can follow the increasing user numbers.

Therefore I wrote a SQL statement searching for newly added users in a specific period:

select distinct Createtime, count (user_id) as new_users 
from user 
where createtime between '2015-12-03 00:00:00' and CURRENT_DATE 
group by createtime 
order by createtime asc;

The following result is shown:

createtime|new_users
---------------------
2015-12-04|    1
2016-01-20|    1     
2016-02-03|    5
2016-02-04|    1

I would like to add up the results to have the current number of users for each of the dates, so the result should look more like this:

createtime|new_users
---------------------
2015-12-04|    1
2016-01-20|    2     
2016-02-03|    7
2016-02-04|    8

Is there a SQL statement or a different way to achieve this? I hope you can help me with this.

Do you store dates or timestamps in the createtime column?

jarlh
– jarlh

2016-09-13 09:28:04 +00:00
Commented Sep 13, 2016 at 9:28 — jarlh
– jarlh, Commented Sep 13, 2016 at 9:28
I use timestamp without timezone

user3906778
– user3906778

2016-09-13 10:53:11 +00:00
Commented Sep 13, 2016 at 10:53 — user3906778
– user3906778, Commented Sep 13, 2016 at 10:53

score 3 · Accepted Answer · 2016-09-13 11:02:09Z

3

You can do that with a window function:

select createtime, sum(new_users) over (order by createtime) as new_users
from (
  select createtime::date as createtime, count(user_id) as new_users 
  from "user"
  where createtime between '2015-12-03 00:00:00' and CURRENT_DATE 
  group by createtime 
) t
order by createtime asc;

user is a reserved keyword and needs to be quoted, otherwise you can't use it as an identifier.

edited Sep 13, 2016 at 11:02

answered Sep 13, 2016 at 9:29

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

user3906778 Over a year ago

How do I eliminate the duplicated timestamps? There are dates from the same day however different during different times a day

user3906778 Over a year ago

Never mind, I solved it with a cast in the from satement (cast createtime as date) and distinct

user330315 Over a year ago

@user3906778: I assumed those were dates, because you didn't do that in your original query either.

user3906778 Over a year ago

Yes you`re right. I edited the post. Thank you for your help! It works great!

Piotr Rogowski · Accepted Answer · 2016-09-13 09:29:22Z

0

try this:

select x.Createtime, 
(select count (sub.user_id) from USER sub 
WHERE sub.createtime between '2015-12-03 00:00:00' and x.Createtime )as new_users 
from user x
where createtime between '2015-12-03 00:00:00' and CURRENT_DATE 
group by createtime 
order by createtime asc;

answered Sep 13, 2016 at 9:29

Piotr Rogowski

3,90021 silver badges24 bronze badges

Collectives™ on Stack Overflow

Postgresql count rows per date adding up

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related