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.
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 (
...
)
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With