How to increase counter in select

Question

I have this kind of situation -

Column A
    1
    0
    0
    0
    1
    0
    0
    0
    0
    1
    0
    1
    0
    0 
    1
    0

I want something like this-

Column A                      Column B
    1                         1
    0                         1
    0                         1
    0                         1
    1                         2
    0                         2
    0                         2
    0                         2
    0                         2
    1                         3
    0                         3
    1                         4
    0                         4
    0                         4
    1                         5
    0                         5

Its like for each occurance of 1 in column A we are increasing the number in column B by one. I want to have this in a select. I can't use loop for this.

I am using SQL-Server 2008 R2. Can anyone please give me idea how it can done. Thanks in advance.

Answer 1

You'll need something to order by. Assuming you have an ID:

SELECT  *   
,       SUM(n) OVER(ORDER BY id)
FROM    ( VALUES (1, 1)
          ,      (2, 0)
          ,      (3, 0)
          ,      (4, 0)
          ,      (5, 1)
          ,      (6, 0)
          ,      (7, 0)
          ,      (8, 0)
          ,      (9, 0)
          ,      (10, 1)
          ,      (11, 0)
          ,      (12, 1)
          ,      (13, 0)
          ,      (14, 0)
          ,      (15, 1)
          ,      (16, 0) 
         ) x (id, n )

Result:

+------+---+-------+
| id   | n | total |
+------+---+-------+
|    1 | 1 |     1 |
|    2 | 0 |     1 |
|    3 | 0 |     1 |
|    4 | 0 |     1 |
|    5 | 1 |     2 |
|    6 | 0 |     2 |
|    7 | 0 |     2 |
|    8 | 0 |     2 |
|    9 | 0 |     2 |
|   10 | 1 |     3 |
|   11 | 0 |     3 |
|   12 | 1 |     4 |
|   13 | 0 |     4 |
|   14 | 0 |     4 |
|   15 | 1 |     5 |
|   16 | 0 |     5 |
+------+---+-------+

edit: The above is only available from sql server 2012, for previous editions the following should work:

WITH test AS
(
    SELECT  *
    FROM    ( VALUES (1, 1)
              ,      (2, 0)
              ,      (3, 0)
              ,      (4, 0)
              ,      (5, 1)
              ,      (6, 0)
              ,      (7, 0)
              ,      (8, 0)
              ,      (9, 0)
              ,      (10, 1)
              ,      (11, 0)
              ,      (12, 1)
              ,      (13, 0)
              ,      (14, 0)
              ,      (15, 1)
              ,      (16, 0) 
             ) x (id, n ) 
)

SELECT  a.id
,       a.n
,       SUM(b.n)
FROM    test a
LEFT JOIN test b
        ON b.id <= a.id
GROUP BY a.id
,        a.n

Result:

+----+---+-------+
| id | n | total |
+----+---+-------+
|  1 | 1 |     1 |
|  2 | 0 |     1 |
|  3 | 0 |     1 |
|  4 | 0 |     1 |
|  5 | 1 |     2 |
|  6 | 0 |     2 |
|  7 | 0 |     2 |
|  8 | 0 |     2 |
|  9 | 0 |     2 |
| 10 | 1 |     3 |
| 11 | 0 |     3 |
| 12 | 1 |     4 |
| 13 | 0 |     4 |
| 14 | 0 |     4 |
| 15 | 1 |     5 |
| 16 | 0 |     5 |
+----+---+-------+

Answer 2

First, you cannot do what you want, because your result depends on the ordering of the rows in the table. Remember: SQL tables represent unordered sets; there is no ordering unless a column makes that explicit.

If you have an ordering column, then think the simplest method in SQL Server 2008 is a correlated subquery or outer apply:

select t.a, t2.b
from t outer apply
     (select count(*) as b
      from t t2
      where t2.id <= t.id and t2.a = 1
     ) t2;

Answer 3

With a cte and window function Row_Number()... However, I should note that it would be best if you replace (Select NULL) in the OVER clause with a proper sequence (ie identity int, datetime).

Declare @YourTable table (ColumnA int)
Insert Into @YourTable values (1),(0),(0),(0),(1),(0),(0),(0),(0),(1),(0),(1),(0),(0),(1),(0)

;with cte as (
    Select *,RN=Row_Number() over (Order By (Select Null)) from @YourTable
)
Select A.ColumnA
      ,ColumnB = sum(B.ColumnA)
 From  cte A
 Join  cte B on (B.RN<=A.RN)
 Group By A.ColumnA,A.RN
 Order By A.RN

Returns

ColumnA ColumnB
1       1
0       1
0       1
0       1
1       2
0       2
0       2
0       2
0       2
1       3
0       3    
1       4
0       4
0       4
1       5
0       5