SQL Server provides GROUP BY CUBE() functionality that we can use in our SQL queries, which creates groups for all possible combinations of columns.
It’s similar to the GROUP BY ROLLUP() functionality, except that it can provide us with more information, due to the fact that it groups all possible combinations.
If you’re getting SQL Server error 10753 that reads “The function ‘LEAD’ must have an OVER clause”, it’s probably because you’re calling the LEAD() function without an OVER clause.
The LEAD() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, be sure to include an OVER clause when calling the LEAD() function.
When we use the GROUP BY clause in SQL Server, we have several options for specifying how the GROUP BY operation is applied. One such option is the ROLLUP modifier. We can use this modifier to create subtotals and grand totals.
If you’re using SQL Server’s APPROX_PERCENTILE_DISC() function, and you’re getting error 10751 that reads “The ORDER BY in WITHIN GROUP clause of ‘APPROX_PERCENTILE_DISC’ function must have exactly one expression“, it’s probably because you’re passing too many ORDER BY expressions.
The APPROX_PERCENTILE_DISC() function accepts just one ORDER BY expression in its WITHIN GROUP clause.
To fix, be sure to use just one ORDER BY expression in the WITHIN GROUP clause when using the APPROX_PERCENTILE_DISC() function.
In SQL Server, the GROUPING() function is used to distinguish the null values that are returned by ROLLUP, CUBE, or GROUPING SETS from standard null values.
Basically it tells us whether a specified column expression in a GROUP BY list is aggregated or not.
The GROUPING() function returns either 1 or 0 (1 indicates that the column expression is aggregated and 0 indicates that it’s not).
The SQL DISTINCT clause allows us to remove redundant duplicate rows from our query results. For example if two rows contain the same values, then only one of them is returned.
But how does DISTINCT handle NULL values?
Let’s find out.
Continue readingSQL Server has a CHECKSUM_AGG() function that allows us to get the checksum of the values in a group. This can be useful for detecting changes in the values in a column.
In SQL Server, the APPROX_PERCENTILE_DISC() function calculates and returns an approximate percentile based on a discrete distribution of the column values.
We pass the desired percentile to the function when we call it.
Continue readingIf you’re getting an error message that reads “The function ‘PERCENT_RANK’ must have an OVER clause with ORDER BY” in SQL Server, it’s probably because you’ve omitted the ORDER BY clause from the OVER clause when using the PERCENT_RANK() function.
The PERCENT_RANK() function requires an OVER clause that contains an ORDER BY clause. This error happens when we include the OVER clause but not the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
In SQL Server, the APPROX_PERCENTILE_CONT() function calculates and returns an approximate percentile based on a continuous distribution of the column value. This is an interpolated value from the set of values in a group based on percentile value and sort specification.