When writing SQL queries, we’ll often use a WHERE clause or HAVING clause to narrow the results down to just those rows that we’re interested in.
But sometimes we might want to reduce the number of rows returned without adding extra filtering criteria. Sometimes we might just want to see a handful of rows, without hundreds, thousands or even millions of rows being returned.
Continue readingWhen using the GROUP BY clause in SQL Server, we can use the GROUPING SETS() option to combine multiple GROUP BY clauses into one GROUP BY clause. When we do this, the results are the equivalent of doing a UNION ALL of the specified groups.
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 reading