Calculate contribution

Question

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...

Maybe duplicated : http://stackoverflow.com/questions/2436820/can-i-do-a-maxcount-in-sql — ahmed abdelqader, Oct 21 '16 at 06:16
You have count(time) function, but you are not aggregating on any thing and since you will get a single count values. No point in taking max for a single value. Have a group by clause, which provides you an expected result — Jim Macaulay, Oct 21 '16 at 06:56
I don't understand what is the point. What are you looking for with `max(count` ? What means? — dani herrera, Oct 21 '16 at 07:06

Pரதீப் · Accepted Answer · 2016-10-21T06:22:17.763

2

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

edited Oct 21 '16 at 06:22

answered Oct 21 '16 at 06:19

Pரதீப்

91,748
19
131
172

Over() is working as you wrote first. Over(partition) gives wrong result – Cine Oct 21 '16 at 06:21
@Cine - ok, May be I over looked it – Pரதீப் Oct 21 '16 at 06:23

Calculate contribution

1 Answers1