CODEWITHSUNDEEP
sqlsql-serverwindow-functions
Manjay_TBAG
Manjay_TBAG

Reputation: 2245

Calculate percentage of salary by each Employee

ID  Name     Age    Salary  running_total   Percentage
1   Abe      61     140000  140000             0
2   Bob      34     44000   184000             0
5   Chris    34     40000   224000             0
7   Dan      41     52000   276000             0
8   Ken      57     115000  391000             0
11  Joe      38     NULL    391000             NULL

I want percentage column of salary as (Salary/TotalSalary) * 100.

I have tried this but it is always giving 0.

  Select [ID]
      ,[Name]
      ,[Age]
      ,[Salary], Sum(Salary) Over(order by Id) As running_total, [Salary]/Sum(Salary)  Over() as Percentage
  From [Company].[dbo].[SalesPerson]

I am even not able to apply multiplication operation. Query gives error on applying multiplication by 100.

Incorrect syntax near the keyword 'Over'.

Upvotes: 0

Views: 5388

Answers (2)

Ruslan K.
Ruslan K.

Reputation: 1981

 Select [ID]
      ,[Name]
      ,[Age]
      ,[Salary]
      ,Sum(Salary) Over(order by Id) As running_total
      ,CAST([Salary] as decimal(18,2)) * 100 / Sum(Salary) Over() as Percentage
  From [Company].[dbo].[SalesPerson]

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Because the division would always result in less than 1 you wouldnot see them unless you multiply by 100.0. Also you should handle null values using coalesce.

Select [ID]
      ,[Name]
      ,[Age]
      ,[Salary], Sum(coalesce(Salary,0)) Over(order by Id) As running_total, 
      100.0*coalesce([Salary],0)/Sum(coalesce(Salary,0))  Over() as Percentage
  From [Company].[dbo].[SalesPerson]

Upvotes: 1

Related Questions