Percentage must be 100

Question

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

Answer 1

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

Answer 2

Hi @Zaran ,

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:

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.

Answer 3

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.