How to use the COUNT function to count up to the current row for each row?

Question

I had trouble phrasing the question, but I think the following example will clear things up.

Context: Trying to create a query for Microsoft Report Builder, need to perform some logic on the data in one of the tables.

I have a SQL Table (let's call it AllEvents) in the database with a format like this:

UserName | Grouping | EventDate | NumberOfEvents
________________________________________________________
Alice | Red Grouping | 1/1/2000 | 1
Alice | Red Grouping | 1/2/2000 | 2
Alice | Red Grouping | 1/3/2000 | 3
Alice | Blue Grouping | 1/4/2000 | 1
Alice | Blue Grouping | 1/5/2000 | 2
Anderson | Red Grouping | 1/3/2000 | 1
Anderson | Blue Grouping | 1/5/2000 | 1
Anderson | Green Grouping | 1/6/2000 | 1
Anderson | Green Grouping | 1/7/2000 | 2

And I want to select the results such that I get an output of the following form, where it counts the number of events for the UserName before and up to the row's EventDate.

UserName | EventDate | EventNumber
_______________________________________
Alice | 1/1/2000 | 1
Alice | 1/2/2000 | 2
Alice | 1/3/2000 | 3
Alice | 1/4/2000 | 4
Alice | 1/5/2000 | 5
Anderson | 1/3/2000 | 1
Anderson | 1/5/2000 | 2
Anderson | 1/6/2000 | 3
Anderson | 1/7/2000 | 4

We can ignore the grouping column, since we're only interested in the rows with the same UserName.

What I have been trying to do is use a subquery like below:

SELECT
 SUM
 (
 CASE WHEN AE.EventDate> AllEvents.EventDate
 THEN 1
 ELSE 0
 END
 )
 OVER (PARTITION BY UserName, Grouping)
FROM AllEvents AS AF
WHERE
 AE.UserName= AllEvents.PatientId
 AND AE.EventDate= AllEvents.EventDate

In the following query:

SELECT
 UserName,
 EventDate,
 [The subquery above] AS EventNumber
FROM
 AllEvents

However, the subquery is returning more than one value and I'm struggling to determine how to troubleshoot.

I have also been toying with the COUNT function, but have failed to have it count conditionally based upon the current row's EventDate value.

Any advice or suggestions would be appreciated. I have been trying to be creative in my approach, but I don't know if I've hit the best method.

• kevinnwhat: Have you looked into window functions ? Is this what you are looking for select UserName, EventDate, row_number over (partition by UserName order by EventDate asc) as EventNumber from AllEvents
• Vérace: My take on this is that NumberOfEvents is a count - yes? All you appear to want is a row_num by date per user? I think that @kevinnwhat 's answer is what you require - maybe SELECT *, ROW_NUMBER...FROM AllEvents? p.s. welcome to the forum! :-)
• Daniel: @kevinnwhat Thank you both so much! This is exactly what I wanted - I didn't realize that the ROW_NUMBER() worked that way with window functions. Such a simple solution too, I feel silly. Please post the answer so I can accept it.
• Daniel: @Vérace Yes, it worked. Thank you both, and thank you for the welcome.
• Vérace: Maybe EventNumber should be EventOrder?

Answer 1

This will do it

select UserName, 
 EventDate, 
 row_number over (partition by UserName order by EventDate asc) as EventNumber 
 from AllEvents