14,301,033 members
Rate this:
See more:
Hi All,i have a table with data like this:
```Name   Month Number
XYZ    Jan   2
XYZ    JAN   4
XYZ    FEB   6
XYZ    FEB   2
XYZ    MAR   10
```

I want output on month basis as with additional column as:
```Name Month Number Count
XYZ   Jan    2       2
XYZ   JAN    4       6
XYZ   FEB    6       6
XYZ   FEB    2       8
XYZ   MAR    10      10
```

Thanks & Regards
Mohd Wasif
Posted
Updated 1-Dec-13 18:40pm
v2

Rate this:

## Solution 3

Here it is :
(I supposed that your table name is Rep)

```with A as
(
SELECT [Name],[Month],[Number]
,row_number() over (partition by name , month order by name, month ) rno
FROM Rep
)
select name , month, number, (
select sum(number) from a a2 where a2.rno<=a1.rno and a2.name=a1.name and a2.month=a1.month ) count
from a a1```

Good Luck
Maciej Los 2-Dec-13 2:17am

+5!
Amir Mahfoozi 2-Dec-13 2:22am

Thank you.
Rate this:

## Solution 1

Check my response for similar post. Hope this will give you a reference to fix your issue.

TSQL query (how to update next row from previous row)[^]
Rate this:

## Solution 2

Hello, try this query:

upd:
```SELECT [xyz].[Name], [xyz].[Month], [xyz].[Number], sm.sub_total
FROM [xyz]
JOIN (SELECT [Name], [Month], [Number],
ROW_NUMBER() OVER(ORDER BY [xyz].[Name], [xyz].[Month]) AS pos
FROM [xyz] ) AS [xyzp]
ON [xyz].[Name] = [xyzp].[Name] AND
[xyz].[Month] = [xyzp].[Month] AND
[xyz].[Number] = [xyzp].[Number]
OUTER APPLY ( SELECT rn.[Name], [Month], SUM(rn.[Number]) AS sub_total
FROM ( SELECT ROW_NUMBER() OVER(ORDER BY [Name], [Month]) AS pos,
[Name], [Month], [Number]
FROM [xyz]
) AS rn
WHERE rn.[Name] = [xyz].[Name] AND
rn.[Month] = [xyz].[Month] AND
rn.pos <= [xyzp].pos
GROUP BY rn.[Name], rn.[Month] ) AS sm```

The query would be simplier if to use 'grouping sets' clause, but it only available in MS SQL 2008 or higher.
v3
Maciej Los 2-Dec-13 2:14am

Above query will return sum for each month, but OP wants to get increasing-sum for numbers.
skydger 2-Dec-13 7:54am

Oops, my mistake, thanks! I've updated solution.