Cine Cine - 1 month ago 4
SQL Question

Calculate contribution

It is not legal to do

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

SELECT id,
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

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()
Comments