CODEWITHSUNDEEP
sql-serveraggregate-functions
getglad
getglad

Reputation: 2562

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.

Upvotes: 4

Views: 6858

Answers (1)

getglad
getglad

Reputation: 2562

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

Upvotes: 8

Related Questions