Answered by:
Sum of amount for all set of number combination
Question

User1034726716 posted
Hello SQL Experts!
I am no sql guru and I have trouble getting the result i wanted. I have tried searching the web for the solution but to no avail. :(
Here's the situation. Given, I have the following table with simple data:
ID Number Amount
1 123 10
2 213 20
3 321 10
4 123 60
5 122 30I want to get the summary of that table by summing the Amount of numbers to result to this:
Number Total
123 100122 20
The first combination should display in the summary. The output above displays the Number '123' since '213' and '321' are a combination of '123'.
I appreciate any guidance. Thank you in advance. :)
Sunday, March 12, 2017 6:06 AM
Answers

User475983607 posted
This might not be the most elegant solution but it works. Create a function that sorts the Number then you can do a GROUP BY.
IF OBJECT_ID (N'dbo.SortNumer', N'FN') IS NOT NULL DROP FUNCTION dbo.SortNumer; GO CREATE FUNCTION dbo.SortNumer(@number INT) RETURNS VARCHAR(100) AS BEGIN DECLARE @sortnums TABLE (digit VARCHAR(1)); DECLARE @sortString VARCHAR(3) = CAST(@number AS VARCHAR(100)); DECLARE @length INT = LEN(@sortString); DECLARE @i INT = 1; DECLARE @sorted VARCHAR(100) = ''; WHILE (@i <= @length) BEGIN INSERT INTO @sortnums VALUES(SUBSTRING(@sortString,@i,1)); SET @i = @i + 1; END; SELECT @sorted = @sorted + digit FROM @sortnums ORDER BY digit RETURN @sorted END; GO SELECT dbo.SortNumer(231)
/* ID Number Amount 1 123 10 2 213 20 3 321 10 4 123 60 5 122 30 */ IF OBJECT_ID('tempdb..#Amount') IS NOT NULL DROP TABLE #Amount CREATE TABLE #Amount ( ID INT IDENTITY(1,1), Number INT, Amount INT ) IF OBJECT_ID('tempdb..#TempAmount') IS NOT NULL DROP TABLE #TempAmount CREATE TABLE #TempAmount ( ID INT, Number INT, Amount INT ) INSERT INTO #Amount(Number, Amount) VALUES (123, 10), (213, 20), (321, 10), (123, 60), (122, 30) INSERT INTO #TempAmount (ID, Number, Amount) SELECT ID, CAST(dbo.SortNumer(Number) AS INT), Amount FROM #Amount SELECT Number, SUM(Amount) AS Total FROM #TempAmount GROUP BY Number
Results
Number Total   122 30 123 100
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Sunday, March 12, 2017 2:30 PM 
User77042963 posted
CREATE TABLE test (id int identity(1,1),Number INT,Amount INT) INSERT INTO test(Number, Amount) VALUES (123, 10),(213, 20),(321, 10),(123, 60),(122, 30) select * from test ;with mycte as (select ID, Number, Amount, substring(Cast(Number as varchar(3)), n, 1 ) splitIDs from test cross apply (values(1),(2),(3)) d(n) Where n <= len(Number) ) ,mycte2 as ( SELECT t1.ID, t1.Amount ,t1.Number , STUFF( ( SELECT distinct ',' + Cast(t2.splitIDs as varchar(5)) FROM mycte t2 WHERE t2.Number = t1.Number FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ids FROM mycte t1 WHERE rn0=1 group by t1.ID, t1.Number , t1.Amount ) ,mycte3 as ( Select Number, Sum( Amount) Over(Partition by ids) total , Row_Number() Over( partition by ids Order by Number) rn2 from mycte2 ) Select Number,total from mycte3 WHERE rn2=1 drop table test
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, March 13, 2017 7:13 PM
All replies

User359936451 posted
You will need to use the GROUP BY phrase on the number field then total the Amount column.
Sample of SQL code here...
http://www.w3resource.com/sql/aggregatefunctions/sumwithgroupby.php
more help...
http://www.oracle.com/technetwork/issuearchive/2013/13jan/o13sql1886636.html
Sunday, March 12, 2017 2:07 PM 
User475983607 posted
This might not be the most elegant solution but it works. Create a function that sorts the Number then you can do a GROUP BY.
IF OBJECT_ID (N'dbo.SortNumer', N'FN') IS NOT NULL DROP FUNCTION dbo.SortNumer; GO CREATE FUNCTION dbo.SortNumer(@number INT) RETURNS VARCHAR(100) AS BEGIN DECLARE @sortnums TABLE (digit VARCHAR(1)); DECLARE @sortString VARCHAR(3) = CAST(@number AS VARCHAR(100)); DECLARE @length INT = LEN(@sortString); DECLARE @i INT = 1; DECLARE @sorted VARCHAR(100) = ''; WHILE (@i <= @length) BEGIN INSERT INTO @sortnums VALUES(SUBSTRING(@sortString,@i,1)); SET @i = @i + 1; END; SELECT @sorted = @sorted + digit FROM @sortnums ORDER BY digit RETURN @sorted END; GO SELECT dbo.SortNumer(231)
/* ID Number Amount 1 123 10 2 213 20 3 321 10 4 123 60 5 122 30 */ IF OBJECT_ID('tempdb..#Amount') IS NOT NULL DROP TABLE #Amount CREATE TABLE #Amount ( ID INT IDENTITY(1,1), Number INT, Amount INT ) IF OBJECT_ID('tempdb..#TempAmount') IS NOT NULL DROP TABLE #TempAmount CREATE TABLE #TempAmount ( ID INT, Number INT, Amount INT ) INSERT INTO #Amount(Number, Amount) VALUES (123, 10), (213, 20), (321, 10), (123, 60), (122, 30) INSERT INTO #TempAmount (ID, Number, Amount) SELECT ID, CAST(dbo.SortNumer(Number) AS INT), Amount FROM #Amount SELECT Number, SUM(Amount) AS Total FROM #TempAmount GROUP BY Number
Results
Number Total   122 30 123 100
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Sunday, March 12, 2017 2:30 PM 
User475983607 posted
You will need to use the GROUP BY phrase on the number field then total the Amount column.
Sample of SQL code here...
http://www.w3resource.com/sql/aggregatefunctions/sumwithgroupby.php
more help...
http://www.oracle.com/technetwork/issuearchive/2013/13jan/o13sql1886636.html
Your answer is off the mark. The OP requested a design that groups all combinations of the Number field; 123, 231, and 321.
Sunday, March 12, 2017 2:33 PM 
User359936451 posted
That is why I suggested using the GROUP BY phrase. Not off the mark its same thing you suggested.
The links I provided show exactly the same type of solution but a bit more elegant.
And allow the user to review a few approaches to the problem to solidify their knowledge.
Sunday, March 12, 2017 2:57 PM 
User475983607 posted
march11
That is why I suggested using the GROUP BY phrase. Not off the mark its same thing you suggested.
The links I provided show exactly the same type of solution but a bit more elegant.
And allow the user to review a few approaches to the problem to solidify their knowledge.
You might want to reread the OPs original question. The basis of the problem is not GROUP BY how to group all combinations of 123, 231, and 321. Neither of the posted links cover how to do this.
Sunday, March 12, 2017 3:00 PM 
User359936451 posted
My mistake, it appears I missread.
Sunday, March 12, 2017 3:03 PM 
User1034726716 posted
Hi Mgebhard,
You're awesome! Thanks for the detailed response. I really appreciate it. I will try this method. :)
Monday, March 13, 2017 6:30 AM 
User1034726716 posted
You will need to use the GROUP BY phrase on the number field then total the Amount column.Hi march11,
I appreciate your response, though that's not what i'm looking for. I know how to group data. :) My problem is to group the set of number combination in the column. I think what Mgebhard solution will work.
Thank you.
Monday, March 13, 2017 6:33 AM 
User77042963 posted
CREATE TABLE test (id int identity(1,1),Number INT,Amount INT) INSERT INTO test(Number, Amount) VALUES (123, 10),(213, 20),(321, 10),(123, 60),(122, 30) select * from test ;with mycte as (select ID, Number, Amount, substring(Cast(Number as varchar(3)), n, 1 ) splitIDs from test cross apply (values(1),(2),(3)) d(n) Where n <= len(Number) ) ,mycte2 as ( SELECT t1.ID, t1.Amount ,t1.Number , STUFF( ( SELECT distinct ',' + Cast(t2.splitIDs as varchar(5)) FROM mycte t2 WHERE t2.Number = t1.Number FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ids FROM mycte t1 WHERE rn0=1 group by t1.ID, t1.Number , t1.Amount ) ,mycte3 as ( Select Number, Sum( Amount) Over(Partition by ids) total , Row_Number() Over( partition by ids Order by Number) rn2 from mycte2 ) Select Number,total from mycte3 WHERE rn2=1 drop table test
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, March 13, 2017 7:13 PM 
User1034726716 posted
Hi Limno,
Your script works well too! Thank you for taking time on looking into this. Much appreciated! :)
Wednesday, March 15, 2017 12:58 AM