Click here to Skip to main content
14,301,033 members
Rate this:
Please Sign up or sign in to vote.
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:
Please Sign up or sign in to vote.

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
   
Comments
Maciej Los 2-Dec-13 2:17am
   
+5!
Amir Mahfoozi 2-Dec-13 2:22am
   
Thank you.
Rate this:
Please Sign up or sign in to vote.

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:
Please Sign up or sign in to vote.

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
Comments
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100