How Do I Sum The Column In Sql Server

Hi,

First of all, you said that you want Due No's like 1 to 12, 13 to 24, etc., but in your expected result [Due No] is 12-24, not 13-24...

Here's my sample (1-12, 13-24, 25-36):

CREATE TABLE #TempData
  ([Collection]   INT,
   [advisor_Code] VARCHAR(50),
   [Plan_name]    VARCHAR(50),
   [advisor_id]   INT,
   [Due_No]       INT);

INSERT INTO #TempData ([Collection], [advisor_Code], [Plan_name], [advisor_id], [Due_No])
VALUES	(3200, '1010200022', 'GIFDL - 36', 127, 1),
	(1000, '1010200022', 'GIFDL - 36', 127, 2),
	(1000, '1010200022', 'GIFDL - 36', 127, 3),
	(1000, '1010200022', 'GIFDL - 36', 127, 4),
	(5000, '1010200022', 'GIFDL - 36', 127, 12),
	(5000, '1010200022', 'GIFDL - 36', 127, 13);


;WITH Ranges
     AS (SELECT 1  AS [RangeFrom],
                12 AS [RangeTo]
         UNION ALL
         SELECT ( [RangeTo] + 1 )  AS [RangeFrom],
                ( [RangeTo] + 12 ) AS [RangeTo]
         FROM   Ranges
         WHERE  [RangeTo] < 36)
SELECT SUM([Collection]) AS [Collection],
       [advisor_Code],
       [Plan_name],
       [advisor_id],
       CAST([RangeFrom] AS VARCHAR(10)) + '-' + CAST([RangeTo] AS VARCHAR(10)) AS [Due_No]
FROM   Ranges AS r
       LEFT OUTER JOIN #TempData AS t
                    ON t.[Due_No] >= r.[RangeFrom]
                       AND t.[Due_No] <= r.[RangeTo]
GROUP  BY [RangeFrom],
          [RangeTo],
          [advisor_Code],
          [Plan_name],
          [advisor_id]
OPTION (MAXRECURSION 32767); 


DROP TABLE #TempData;

Max [Due_No] is limited to 36 (WHERE [RangeTo] < 36). Instead of CTE you can use a table or table variable.

Result:

Collection	advisor_Code	Plan_name	advisor_id	Due_No
11200		1010200022	GIFDL - 36	127		1-12
5000		1010200022	GIFDL - 36	127		13-24
NULL		NULL		NULL		NULL		25-36