Cine Cine - 1 year ago 75
SQL Question

Calculate contribution

It is not legal to do

, so how do I accomplish calculating the contribution as shown here (and also get the other columns)

Avg(time) AS avgSec,
Stdev(time) AS stdevSec,
Count(time) AS cnt,
Avg(time)*Count(time)/max(Count(time)) AS contribution
FROM ...very long and complex query...

Answer Source

Use MAX()OVER() window aggregate function to get maximum count out of all records

Here is the correct way

Avg(time)*Count(time)/max(Count(time)) over()