CODEWITHSUNDEEP
sqlsql-server
DRUIDRUID
DRUIDRUID

Reputation: 369

convert to show percentage of the sum of 2 or more combined columns

I have looked at the similar questions and was unable to find anything that answered my question.

I have a column that is the calculation of other columns of which sum I would like to divide by a set number and show results in percentage from. I have tried the below query and am able to show in decimal. Basically find out what percentage of UPH an associate is doing...This way I can tell if they are slacking in their department or exceling. I am using 150 as my base rate

SELECT  
ID
  , DeptCode AS 'DC'
  , OpCode AS 'OC' 
  ,Units = sum(Units)
  ,UPH = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60  as decimal(10,0))
  ,Percentage = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60  as decimal(10,0)) / 150 


FROM dbo.TimeLog 
 WITH (NOLOCK)

WHERE DeptCode = 'FB'
  and OpCode   = 'PU'
  and Units IS NOT NULL
GROUP BY 
    ID
  , DeptCode
  , OpCode

Here is my result...

ID      DC  OC  Units   UPH Percentage
11259   FB  PU  553     114 0.760000
2375935 FB  PU  263     70  0.466666

I would like to see column 'Percentage' like the below

ID      DC  OC  Units   UPH Percentage
11259   FB  PU  553     114 76%
2375935 FB  PU  263     70  47%

Any help would be appreciated and I apologize in advance if this is elementary for a lot you, I am relatively new to SQL

Upvotes: 0

Views: 69

Answers (2)

Somdip Dey
Somdip Dey

Reputation: 3386

Try this instead (SQL 2012 and later):

Percentage = CONCAT(cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60 as decimal(10,0)), '%')

Refer: https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql

Try this instead (SQL 2008):

(convert(varchar,cast((isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60)/150*100 as as decimal(10,0))) + '%') as Percentage

Refer: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-transact-sql

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can use format(decimal, 'p') as below:

      ,Percentage = Format(cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60  as decimal(10,0)) / 100 , 'p')

Upvotes: 0

Related Questions