When using the APPROX_PERCENTILE_DISC() function in SQL Server, you may get an error that reads “The function ‘APPROX_PERCENTILE_DISC’ must have a WITHIN GROUP clause“. If you get this error, it’s because you omitted the WITHIN GROUP clause when using the APPROX_PERCENTILE_DISC() function.
To fix this issue, make sure you include the WITHIN GROUP clause whenever you use the APPROX_PERCENTILE_DISC() function.
If you’re getting SQL Server error 10751 that reads “The ORDER BY in WITHIN GROUP clause of ‘APPROX_PERCENTILE_CONT’ function must have exactly one expression” it’s probably because you’re using too many ORDER BY expressions with the APPROX_PERCENTILE_CONT() function.
The APPROX_PERCENTILE_CONT() function requires the WITHIN GROUP clause, and that clause requires an ORDER BY sub-clause. However, that ORDER BY sub-clause requires exactly one expression – no more, no less. So, you can’t pass multiple expressions, and you can’t pass zero expressions. It must be exactly one expression.
To fix this issue, be sure to have just one ORDER BY expression in the WITHIN GROUP clause when using the APPROX_PERCENTILE_CONT() function.
When using the GROUP BY clause in SQL Server, we can use the ROLLUP modifier to create subtotals and grand totals, etc.
You may have seen two variations of this. One as GROUP BY ROLLUP () and the other as GROUP BY ... WITH ROLLUP.
You may be wondering which one you should use?
As it turns out, Microsoft recommends that we use the first syntax; GROUP BY ROLLUP (). The other syntax is provided for backward compatibility only.
In SQL NULL is a special value, or mark, that is used to indicate the absence of any data value. And this is the case with SQL Server.
But SQL Server also has a special use of NULL in certain cases.
If you’re getting SQL Server error msg 402 that tells you the “data types” are “incompatible in the approx_percentile_cont operator“, it’s probably because you’re trying to apply the APPROX_PERCENTILE_CONT() function against a non-numeric column (or one that doesn’t evaluate to a numeric type).
When using the APPROX_PERCENTILE_CONT() function, the ORDER BY expression in the WITHIN GROUP clause must evaluate to an exact or approximate numeric type. Other data types are not allowed, and will result in the above error.
To fix this issue, be sure to apply the function against a numeric column/expression.
Continue readingIf you’re getting SQL Server error 10754 that reads “The function ‘APPROX_PERCENTILE_CONT’ must have a WITHIN GROUP clause” it’s probably because you’re calling the APPROX_PERCENTILE_CONT() function, but you’ve omitted the WITHIN GROUP clause.
To fix this issue, add a WITHIN GROUP clause to the function (and make sure it has an ORDER BY clause).
In SQL Server the VARP() function returns the statistical variance for the population for all values in the specified expression.
In SQL Server the VAR() function returns the statistical variance of all values in the specified expression.
In SQL Server the STDEVP() function returns the statistical standard deviation for the population for all values in the specified expression.
In SQL Server the STDEV() function returns the statistical standard deviation of all values in a specified expression.
So we can use this function to get the standard deviation of all values in a column.
Continue reading