2

I am using Metabase with a PostgreSQL implementation.

Running Count of Occurrences

I'd like to achieve the outcome in the photo. I wish to include a column that counts how many times a field has occurred above. It should include the current instance as well.

Other examples I've seen have simply counted total occurrences. As I am ordering by date, I do not wish to count occurrences that happen after the date.

Cœur
  • 37,241
  • 25
  • 195
  • 267
user9925471
  • 23
  • 2

1 Answers1

2

You can use window functions in Postgres:

select name, date, row_number() over (partition by name order by date) as seqnum
from t;

You probably also want order by date at the end of the query to guarantee that the results are in date order.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is working for me! ... with one small issue. There are some cases where multiple instances occur on the same day. I do not understand why it may list: David - 2, David - 1, David - 3... `order by date, seqnum`seems to address it though. – user9925471 Jun 11 '18 at 14:48