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.
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
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 |
+----+---+-------+
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;
