We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

Sum of amount for all set of number combination[Answered] RSS

10 replies

Last post Mar 15, 2017 12:58 AM by vinz

• vinz

All-Star

102952 Points

19469 Posts

MVP

Sum of amount for all set of number combination

Mar 12, 2017 06:06 AM|vinz|LINK

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                 30

I want to get the summary of that table by summing the Amount of numbers to result to this:

Number  Total
123          100

122          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. :)

Vincent Maverick Durano
Microsoft MVP, CodeProject MVP, C# Corner MVP
Blog | Twitter | Linkedin
• march11

Contributor

2155 Points

2142 Posts

Re: Sum of amount for all set of number combination

Mar 12, 2017 02:07 PM|march11|LINK

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/aggregate-functions/sum-with-group-by.php

more help...

http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13sql-1886636.html

• mgebhard

All-Star

54641 Points

24475 Posts

Re: Sum of amount for all set of number combination

Mar 12, 2017 02:30 PM|mgebhard|LINK

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```

• mgebhard

All-Star

54641 Points

24475 Posts

Re: Sum of amount for all set of number combination

Mar 12, 2017 02:33 PM|mgebhard|LINK

march11

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/aggregate-functions/sum-with-group-by.php

more help...

http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13sql-1886636.html

Your answer is off the mark.  The OP requested a design that groups all combinations of the Number field; 123, 231, and 321.

• march11

Contributor

2155 Points

2142 Posts

Re: Sum of amount for all set of number combination

Mar 12, 2017 02:57 PM|march11|LINK

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.

• mgebhard

All-Star

54641 Points

24475 Posts

Re: Sum of amount for all set of number combination

Mar 12, 2017 03:00 PM|mgebhard|LINK

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 re-read 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.

• march11

Contributor

2155 Points

2142 Posts

Re: Sum of amount for all set of number combination

Mar 12, 2017 03:03 PM|march11|LINK

My mistake, it appears I miss-read.

• vinz

All-Star

102952 Points

19469 Posts

MVP

Re: Sum of amount for all set of number combination

Mar 13, 2017 06:30 AM|vinz|LINK

Hi Mgebhard,

You're awesome! Thanks for the detailed response. I really appreciate it. I will try this method. :)

Vincent Maverick Durano
Microsoft MVP, CodeProject MVP, C# Corner MVP
Blog | Twitter | Linkedin
• vinz

All-Star

102952 Points

19469 Posts

MVP

Re: Sum of amount for all set of number combination

Mar 13, 2017 06:33 AM|vinz|LINK

march11

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.

Vincent Maverick Durano
Microsoft MVP, CodeProject MVP, C# Corner MVP
Blog | Twitter | Linkedin
• limno

All-Star

123252 Points

10024 Posts

Moderator

Re: Sum of amount for all set of number combination

Mar 13, 2017 07:13 PM|limno|LINK

```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```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• vinz

All-Star

102952 Points

19469 Posts

MVP

Re: Sum of amount for all set of number combination

Mar 15, 2017 12:58 AM|vinz|LINK

Hi Limno,

Your script works well too! Thank you for taking time on looking into this. Much appreciated! :)

Vincent Maverick Durano
Microsoft MVP, CodeProject MVP, C# Corner MVP
Blog | Twitter | Linkedin