从表中每行除以同一表的总和(Divide from table each row with the sum of the same table)

从表中每行除以同一表的总和(Divide from table each row with the sum of the same table)

我有一个显示事件的查询，对于这个事件有持续时间。 为了获得持续时间，我在查询中进行了计算：

Sum(cast(EventEndDateTime - EventStartDateTime as float)) as Duration,

桌子：

Event Duration [Pr 10,48 [Al 9,89 [To 1,32 [Co 0,41 [Gh 0,33

而且我必须将此行的每一行与列持续时间的总和相除。 例如：列持续时间的总和= 22,43。 - >（10,48 / 22,43）* 100 = 46,72％

我试过这个：

Sum(cast(EventEndDateTime - EventStartDateTime as float)) / (SELECT Sum(cast(EventEndDateTime - EventStartDateTime as float)) From Event) as SumOfDuration

但这给了我不正确的结果。 我使用SSRS

I have got a query that shows Events and for this events there are durations. To get the Duration I did a calculation in my query:

Sum(cast(EventEndDateTime - EventStartDateTime as float)) as Duration,

The Table:

Event Duration [Pr 10,48 [Al 9,89 [To 1,32 [Co 0,41 [Gh 0,33

And I must divide each of this row with the sum of the column Duration. For Example: The Sum of the column Duration is =22,43. --> (10,48/22,43)*100 = 46,72%

I tried this here:

Sum(cast(EventEndDateTime - EventStartDateTime as float)) / (SELECT Sum(cast(EventEndDateTime - EventStartDateTime as float)) From Event) as SumOfDuration

But this gave me not the correct result. I use SSRS

最满意答案

SQL方法：

select event, duration, duration/sum(duration) over (partition by 1) percent from ( select event, sum(cast(EventEndDateTime - EventStartDateTime as float)) as Duration from t group by event) as t

SQL approach:

select event, duration, duration/sum(duration) over (partition by 1) percent from ( select event, sum(cast(EventEndDateTime - EventStartDateTime as float)) as Duration from t group by event) as t