asked on # t-sql Group of records whose sum Equal a specific Number

I am trying to get each group of records whose sum equal 3000.....I'll try to explain with an example

Give this records below

Id Number 1

1 1000

2 2000

3 3000

4 50

5 100

6 2500

7 350

Result would be, where I would find the min and max of the ID where the sum totals 3000 for the records

Min Max Sum

1 2 3000

3 3 3000

4 7 3000

Here is the code to create the example table

create table #Example

(ID int identity(1,1)

,Number1 int)

insert into #Example

(Number1)

select 1000 union

select 2000 union

select 3000 union

select 50 union

select 100 union

select 2500 union

select 350

Give this records below

Id Number 1

1 1000

2 2000

3 3000

4 50

5 100

6 2500

7 350

Result would be, where I would find the min and max of the ID where the sum totals 3000 for the records

Min Max Sum

1 2 3000

3 3 3000

4 7 3000

Here is the code to create the example table

create table #Example

(ID int identity(1,1)

,Number1 int)

insert into #Example

(Number1)

select 1000 union

select 2000 union

select 3000 union

select 50 union

select 100 union

select 2500 union

select 350

Microsoft SQL ServerMicrosoft SQL Server 2008

My numbers increment in very small amounts (the ones that I gave are just for an example). The final sum of each rolling sum will come really close to 3000. So I guess, it would be best to get the sum that comes closest to 3000 (less than or equal to 3000).

So given your example, the result set would be

Min Max Sum

1 2 2500

3 3 3000

4 7 3000

8 9 1250

10 10 2300

11 11 900

So given your example, the result set would be

Min Max Sum

1 2 2500

3 3 3000

4 7 3000

8 9 1250

10 10 2300

11 11 900

I am sure there is a better way to do it. But this will work as well.

drop table #Example

drop table #temp

create table #Example

(ID int identity(1,1)

,Number1 int)

insert into #Example

(Number1)

select 1000 union

select 2000 union

select 3000 union

select 50 union

select 100 union

select 2500 union

select 350

select * from #Example

select * into #temp

from #Example

select ID as 'min',ID as 'max','3000' as 'sum'

from #Example

where Number1=3000

union

select t.ID as 'min',e.ID as 'max','3000' as'sum'

from #temp t

join #Example e

on t.ID<>e.ID

where t.ID<e.ID

group by t.Number1,e.Number1,t.ID,e.ID

having (t.Number1 + e.Number1)=3000

drop table #Example

drop table #temp

create table #Example

(ID int identity(1,1)

,Number1 int)

insert into #Example

(Number1)

select 1000 union

select 2000 union

select 3000 union

select 50 union

select 100 union

select 2500 union

select 350

select * from #Example

select * into #temp

from #Example

select ID as 'min',ID as 'max','3000' as 'sum'

from #Example

where Number1=3000

union

select t.ID as 'min',e.ID as 'max','3000' as'sum'

from #temp t

join #Example e

on t.ID<>e.ID

where t.ID<e.ID

group by t.Number1,e.Number1,t.ID,e

having (t.Number1 + e.Number1)=3000

This solution can be a little slow when the table is bigger but couldn't find a better way to do it:

```
;WITH CTE_SUM (MinID, MaxID, MySum)
AS
(
SELECT ID, ID, Number1
FROM #Example
WHERE ID = 1
UNION ALL
SELECT
CASE
WHEN E.Number1+S.MySum > 3000 THEN E.ID
ELSE S.MinID
END,
E.ID,
CASE
WHEN E.Number1+S.MySum > 3000 THEN E.Number1
ELSE E.Number1+S.MySum
END
FROM #Example E
INNER JOIN CTE_SUM S ON E.ID=S.MaxID+1
)
SELECT MinId, MAX(MaxId) MaxId, MAX(MySum) MySum
FROM CTE_SUM
GROUP BY MinId
ORDER BY MinId
```

Hi Mayank,

The result set returns 3 records, it doesn't add the rest of the records

also, try, union all so it can enter the records in correct order....sorry about that

create table #Example

(ID int identity(1,1)

,Number1 int)

insert into #Example

(Number1)

select 1000 union all

select 2000 union all

select 3000 union all

select 50 union all

select 100 union all

select 2500 union all

select 350

The result returns

min max sum

1 2 3000

3 3 3000

it doesn't sum the rest of the records, so the result set would be

min max sum

1 2 3000

3 3 3000

4 7 3000

Thanks again for your help!

The result set returns 3 records, it doesn't add the rest of the records

also, try, union all so it can enter the records in correct order....sorry about that

create table #Example

(ID int identity(1,1)

,Number1 int)

insert into #Example

(Number1)

select 1000 union all

select 2000 union all

select 3000 union all

select 50 union all

select 100 union all

select 2500 union all

select 350

The result returns

min max sum

1 2 3000

3 3 3000

it doesn't sum the rest of the records, so the result set would be

min max sum

1 2 3000

3 3 3000

4 7 3000

Thanks again for your help!

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Id Number 1

1 1000

2 1500

3 3000

4 50

5 100

6 2500

7 350

8 500

9 750

10 2300

11 900

Now, which results should be expected?