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

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
)

FROM AllEvents AS AF

WHERE
AND AE.EventDate= AllEvents.EventDate
``````

In the following query:

``````SELECT
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.

• 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 Sep 6, 2019 at 16:20
• 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! :-) Sep 6, 2019 at 16:35
• @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. Sep 6, 2019 at 17:44
• @Vérace Yes, it worked. Thank you both, and thank you for the welcome. Sep 6, 2019 at 17:45
• Maybe `EventNumber` should be `EventOrder`? Sep 6, 2019 at 18:19

``````select UserName,