Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using PERCENTILE_DISC with Aggregate functions

I am trying to use PERCENTILE_DISC with a number of other built in aggregate functions. The code I am attempting reads like this, but it fails:

SELECT
    [DataPoint] AS [DataPoint],
    MIN([Value]) AS [Value MIN],
    MAX([Value]) AS [Value MAX],
    AVG([Value]) AS [Value AVG],
    PERCENTILE_DISC(0.5)
        WITHIN GROUP 
        (ORDER BY [Value])
        OVER 
        (PARTITION BY [DataPoint])
        AS MedianCont
    FROM [Table] AS [Table]
    WHERE ([DataPoint]
        IN (
            ...
        )
    )
    GROUP BY [DataPoint]

So this works...

SELECT
    Distinct [DataPoint],
    PERCENTILE_DISC(0.5)
    WITHIN GROUP 
    (ORDER BY [Value])
    OVER 
    (PARTITION BY [DataPoint])
    AS MedianCont
    FROM [Table] AS [Table]
    WHERE ([DataPoint]
        IN (
            ...
        )
    )

And this works...

SELECT
    [DataPoint] AS [DataPoint],
    MIN([Value]) AS [Value MIN],
    MAX([Value]) AS [Value MAX],
    AVG([Value]) AS [Value AVG]
    FROM [Table] AS [Table]
    WHERE ([DataPoint]
        IN (
            ...
        )
    )
    GROUP BY [DataPoint]

But when I try to combine them, it wants me to declare Value in the Group By clause, which I do not want because I want a distinct list of DataPoint's, not a value per DataPoint.

Column 'Value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
like image 683
getglad Avatar asked Feb 06 '23 07:02

getglad


1 Answers

It would appear that using the over clause, thus creating a 'window function', separates the aggregate function from the select statement.

SELECT
    Distinct [DataPoint],
    MIN([Value]) OVER (PARTITION BY [DataPoint]) AS [Value MIN],
    MAX([Value]) OVER (PARTITION BY [DataPoint]) AS [Value MAX],
    AVG([Value]) OVER (PARTITION BY [DataPoint]) AS [Value AVG],
    PERCENTILE_DISC(0.5)
        WITHIN GROUP 
        (ORDER BY [Value])
        OVER 
        (PARTITION BY [DataPoint])
        AS MedianCont
    FROM [Table] AS [Table]
    WHERE ([DataPoint]
        IN (
            ...
        )
    )
like image 100
getglad Avatar answered Feb 16 '23 11:02

getglad