question

Zaran-3086 avatar image
0 Votes"
Zaran-3086 asked EchoLiu-msft commented

Percentage must be 100

Hi,

Could you please help me how to get the total percentage as 100% from below dataset through T-SQL?

0.66%, 0.66%, 10.63%, 21.71%, 36.84%, 23.03%, 6.48% -> must be equal 100.00%
0.61%, 0.66%, 10.65%, 21.45%, 36.48%, 23.63%, 6.76% -> must be equal 100.00%

Thanks

sql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
Zaran-3086 avatar image Zaran-3086 YitzhakKhabinsky-0887 ·

DECLARE @T TABLE
(
Total Decimal(13,8)
)
DECLARE @Total Decimal(13,8)

INSERT INTO @T VALUES(0.66)
INSERT INTO @T VALUES(0.66)
INSERT INTO @T VALUES((10.63)
INSERT INTO @T VALUES(21.71)
INSERT INTO @T VALUES(36.84)
INSERT INTO @T VALUES(23.03)
INSERT INTO @T VALUES(6.48)

result must be -> sum(Total)-> @Total=100.00%


for the second dataset :
INSERT INTO @T VALUES(0.61)
INSERT INTO @T VALUES(0.66)
INSERT INTO @T VALUES(10.65)
INSERT INTO @T VALUES(21.45)
INSERT INTO @T VALUES(36.48)
INSERT INTO @T VALUES(23.63)
INSERT INTO @T VALUES(6.76)

result must be -> sum(Total) -> @Total=100.00%

0 Votes 0 ·

Hi @Zaran-3086,

Do you have any grouping basis for these two data sets? How do you distinguish them into two grouping sets? If we don't know this, we can't sum up in groups.

Echo

0 Votes 0 ·

Could you have any update?Did the following methods solve your problem?If not, please provide more details.If it is resolved,please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
VladimirMoldovanenko-3700 avatar image
0 Votes"
VladimirMoldovanenko-3700 answered

here is one of answers, wild guess, as question is poorly formulated.

 DROP TABLE IF EXISTS #t
    
 SELECT identity (int) ID, p1.v
 INTO #t
 FROM string_split('0.66%, 0.66%, 10.63%, 21.71%, 36.84%, 23.03%, 6.48%', ',') p
 CROSS APPLY(SELECT v = TRY_CAST(REPLACE(p.value, '%','') as decimal(9,2))) p1
    
    
 SELECT 
     t.ID
     ,t.v
     ,[100%] = CASE WHEN t.ID = 1 THEN (100 - SUM(t.v)OVER()+ t.v) ELSE t.v END
 FROM #t t
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Zaran-3086,

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data.

Since you did not describe your rules in detail, the following methods are for reference only:

 CREATE TABLE #test(val1 decimal(5,2),val2 decimal(5,2),)
 INSERT INTO #test values(0.66,0.61),(0.66,0.66),(10.63,10.65)
                         ,(21.71,21.45),(36.84,36.48),(23.03,23.63),(6.48,6.76)
    
    
 SELECT CAST(ROUND(SUM(val1), 0) as varchar) + '%' as result1 ,
 CAST(ROUND(SUM(val2), 0) as varchar) + '%' as result2
 FROM #test

Output:
103646-image.png

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.



image.png (1.5 KiB)
image.png (1.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

The values you posted do not equal 100.00%. So I am unclear what you are asking.

I assume your numbers are calculated by dividing something. In that case adding 2 decimal precision numbers together will not always equal 100.00 due to rounding. You would need to calculate your numbers to 3 decimal precision to have better luck. However, it may still not add up to 100.00. This is a common problem with percentage calculations.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.