percentile_cont with aggregate Function

Question

I'm using SQL Server 2012 and trying to calculate some aggregate functions and percentiles on a data set with two columns (id and time). In my research I found some solutions, but they don't work for me (maybe it's because of my outdated SQL version)

id	time_seconds
1	120
2	10
3	50
4	80
5	60
6	42
7	96

I'll tried something like that:

SELECT 
    MIN(time_seconds) AS min,
    MAX(time_seconds) AS max,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY time_seconds) OVER () AS '95 percentil'
from table 

If i run this query, it wants me to make an include "time_seconds" in Group by, but then the aggregate function doesnt work anymore.

The Output id like to become is something like this :

min	max	95 percentil
10	120	110

Thanks for your help!

Answer 1

Unfortunately PERCENTILE_CONT works as a window function only, not as an aggregate function. This is a (rare) case when select distinct may come useful:

select distinct
    min(time_seconds) over() as min_time_seconds,
    max(time_seconds) over() as max_time_seconds,
    percentile_cont(0.95) within group (order by time_seconds) over () as percentil_95
from mytable 

Basically this takes the problem the opposite way: since we can't have an aggregate percentile computation, we turn other aggregate functions to window functions: the window computations yield the same values on all rows, so we can then deduplicate the resultset with distinct.

min_time_seconds	max_time_seconds	percentil_95
10	120	112.8

fiddle - this is SQL Server 2014, since db<>fiddle does not support 2012.