CODEWITHSUNDEEP
sqlreporting-services
M.JAY
M.JAY

Reputation: 193

Divide from table each row with the sum of the same table

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

Upvotes: 0

Views: 214

Answers (2)

mxix
mxix

Reputation: 3659

You can do it in SSRS.

Let's say you have a column with Duration, and a details row group with Event. With an expression like:

SUM(Fields!Duration.Value) / SUM(Fields!Duration.Value,"Events")

Replace "Events" with the name of your detail group

SUM has a SCOPE optional parameter

Upvotes: 1

vercelli
vercelli

Reputation: 4757

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

Upvotes: 0

Related Questions