Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
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!
SELECT DISTINCT
e1.id
, case when e1.Number1 = 3000 then NULL else e2.id end
, case when e1.Number1 = 3000 then e1.Number1 else (e1.Number1 + e2.NUmber1) end
FROM Example e1
CROSS JOIN Example e2
WHERE e1.Number1 = 3000
OR (e1.Number1 + e2.NUmber1) between 2700 and 3300 -- 10% either side
ORDER BY
e1.id
, case when e1.Number1 = 3000 then NULL else e2.id end
CREATE TABLE Example
([Id] int, [Number1] int)
;
INSERT INTO Example
([Id], [Number1])
VALUES
(1, 1000),
(2, 1500),
(3, 3000),
(4, 50),
(5, 100),
(6, 2500),
(7, 350),
(8, 500),
(9, 750),
(10, 2300),
(11, 900)
;
| id | | |
|----|--------|------|
| 1 | 10 | 3300 |
| 2 | 2 | 3000 |
| 3 | (null) | 3000 |
| 4 | 3 | 3050 |
| 5 | 3 | 3100 |
| 6 | 7 | 2850 |
| 6 | 8 | 3000 |
| 6 | 9 | 3250 |
| 7 | 6 | 2850 |
| 8 | 6 | 3000 |
| 8 | 10 | 2800 |
| 9 | 6 | 3250 |
| 9 | 10 | 3050 |
| 10 | 1 | 3300 |
| 10 | 8 | 2800 |
| 10 | 9 | 3050 |
| 10 | 11 | 3200 |
| 11 | 10 | 3200 |
also see: http://sqlfiddle.com/#!6/bcdcc/5
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
-
Windows Server 2016Certification: MCSE Microsoft Certified Systems Engineer - Installatio… 312 lessons
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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?