Database Management Systems
SQL Server has a GROUPING_ID() function that returns the level of grouping based on its arguments.
We pass one or more column expressions to the function, and it computes the level of grouping. The column expressions must match the ones provided by the GROUP BY clause.
If you’re getting an error in PostgreSQL that reads something like “function lag(numeric, numeric) does not exist“, it could be because your second argument is the wrong data type.
The second argument to the lag() function is optional, but if it’s provided, it must be an integer.
So to fix this issue, make sure the second argument is an integer. Alternatively, you can omit the second argument altogether if you’re happy to use the default value of 1.
When 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).
SQL 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