# Find total and percentage • ### Question

• Hi Team,

I have requirement as below.

col A            Col b          SUMcol              %col

category 1       1500

category 2        2000

category 3       800

.

.

category 6        1000

So I want to calculate SUMCol as sum(col b) i,e->1500+2000+800+. ..+1000 = TOTAL VALUE

Then i want to calculate % col as  colb/Sumcol*100 for each row.-->1500/total value*100  , 200/total value*100 and so on..

How can I do these at query level.

Kindly help

Thank you

sania

Thursday, March 21, 2019 2:35 PM

### All replies

• ```CREATE TABLE #tmp (ColA VARCHAR(20), ColB INT)
INSERT INTO #tmp VALUES ('category1',1500), ('category2',2000), ('category3',800), ('category6',1000)

SELECT ColA, ColB, SUM(ColB) OVER() SumCol,  CAST((ColB*1.0/SUM(ColB) OVER())*100 AS DECIMAL(4,2)) AS '%col'
FROM #tmp

DROP TABLE #tmp```

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

[User Page]     [MSDN Page]     [Blog]     [Linkedin]

Thursday, March 21, 2019 2:42 PM
• select [col a],[col b], sum ([col b]), avg[(sum([col b])/6)*100] as avg from table

https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

Thursday, March 21, 2019 2:47 PM
• If you want your SUMCol as running total, you change Ousama's query a little:

SELECT ColA, ColB, SUM(ColB) OVER(Order by ColA) SumCol,  CAST((ColB*1.0/SUM(ColB) OVER())*100 AS DECIMAL(4,2)) AS '%col'
FROM #tmp

Thursday, March 21, 2019 3:32 PM