Sql Keeping Count Of Occurrences

I've the following problem I need to solve in SQL. Let's say that I have a table with 2 columns: Date | Code -------- 0 | 25 1 | 22 2 | 23 3 | 2

Solution 1:

The simplest (and probably most efficient) approach is to use ROW_NUMBER():

SELECT [Date], Code, [Count] =ROW_NUMBER() OVER (PARTITIONBY Code ORDERBY [Date])
 FROM dbo.YourTableName
 ORDERBY [Date];

For fun, you can also solve it this way in SQL Server 2012. If Date is unique:

SELECT [Date], Code, [Count] =COUNT(*) OVER (PARTITIONBY Code ORDERBY [Date]
  RANGE UNBOUNDED PRECEDING)
 FROM dbo.YourTable
 ORDERBY [Date];

Or more simply:

SELECT [Date], Code, [Count] =COUNT(*) OVER (PARTITIONBY Code ORDERBY [Date])
 FROM dbo.YourTable
 ORDERBY [Date];

If Date is not unique, and if you don't want ties (same count for identical combinations of date+code), you need to use the more expensive ROWS, which uses on on-disk spool:

SELECT [Date], Code, [Count] =COUNT(*) OVER (PARTITIONBY Code ORDERBY [Date]
  ROWS UNBOUNDED PRECEDING)
 FROM dbo.YourTable
 ORDERBY [Date];

You may want to try each of these options on your table to see what the performance is like.

Solution 2:

If i understood you correctly you want to add extra column that will tell you how many times that Code occurred.

select*, 
COUNT(1) over (partitionby Code) 
fromTable

Solution 3:

It looks like you want a running count of the code values. If so, then you can use the following query to get a running count:

SELECTdate,
  code, 
  (SELECT count(code)
   FROM yourtable b 
   WHERE b.date <= a.dateand a.code = b.code) AS TotalCount
FROM yourtable a

See SQL Fiddle with Demo

Or you can use:

SELECT a.date,
  a.code, 
  count(a.code) AS TotalCount
FROM yourtable a
cross join yourtable b
WHERE b.date <= a.dateand a.code = b.code
groupby a.date, a.code
orderby a.date;

See SQL Fiddle with Demo

Note, this will work fine on smaller tables but on larger tables there will be issues. (Thanks @AaronBertrand)