SQL Dependent Percentage Sample data



Excepted output 



Math Operation


Sum all Period time for each EmpName as Total Employee Time
Sum Similar Tasks For EmpName, divide it by Total Employee Time and multiply by 100%


please help me 

I am using SQL Server Management 2008 and here is my try:

select EmpName,Task,Period_Time, 
sum(Period_Time)/(select sum(Period_Time) as total from dbo.TableName) 
as percentage 
from  dbo.TableName 
group by EmpName,Task,Period_Time 
order by EmpName
You were close with your approach. The two issues with it that I can see are:


The subquery is not correlated with the outer query. Your subquery needs to have a WHERE clause to limit the rows only to those where EmpName is the same as the outer query's EmpName.

In order to be able to reference the outer query's columns, you need to assign a different alias to either instance of dbo.TableName. It would probably be best to assign one to each – something like this:

SELECT
  ...
  (SELECT ... FROM dbo.TableName AS b WHERE b.EmpName = a.EmpName)
FROM
  dbo.TableName AS a
...

The main query does not need to group by Period_Time, because it is aggregating that value.

Just remove the Period_Time column from both the GROUP BY and SELECT.


So, this is how your query could look like after addressing both issues:

SELECT
  EmpName,
  Task,
  Percentage  = SUM(Period_Time) * 100.0
              / (
                  SELECT
                    SUM(b.Period_Time)
                  FROM
                    dbo.TableName AS b
                  WHERE
                    b.EmpName = a.EmpName
                )
FROM
  dbo.TableName AS a
GROUP BY
  EmpName,
  Task
ORDER BY
  EmpName
;


However, you could do better than using a correlated subquery here. Since SQL Server 2005, Transact-SQL has started supporting window aggregate functions. They allow you to obtain aggregate results alongside detail results and use both in various calculations – exactly what you need for your problem.

Basically, the syntax would go like this:

SELECT
  SomeKey,
  DetailData,
  TotalData = SUM(DetailData) OVER (PARTITION BY SomeKey),
  -- or you can get percentage directly, like this:
  -- Percentage = DetailData * 100.0 / SUM(DetailData) OVER (PARTITION BY SomeKey)
FROM
  dbo.SomeTable
;


There is only one issue: you cannot calculate the total Period_Time like this:

SUM(Period_Time) OVER (PARTITION BY EmpName)


because in your case Period_Time would be an invalid reference: that column is not in GROUP BY and may not be referenced without being enclosed in a (non-window) aggregate function. Your main query already aggregates it using SUM – that is how it can be used in SUM ... OVER as well:

SUM(SUM(Period_Time)) OVER (PARTITION BY EmpName)


For reference, here is an example of a complete query that calculates percentage using window aggregation instead of a correlated subquery:

SELECT
  EmpName,
  Task,
  Percentage  = SUM(Period_Time) * 100.0 / SUM(SUM(Period_Time)) OVER (PARTITION BY EmpName)
FROM
  dbo.TableName
GROUP BY
  EmpName,
  Task
ORDER BY
  EmpName
;