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 readingIn 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.
Like most other RDBMSs, SQL Server has an AVG() function that returns the average of the values in a group.
Below are examples that demonstrate how the AVG() function works in SQL Server.
Some relational database management systems (RDBMSs) have a MEDIAN() function that calculates the median value within a range of values.
It returns the middle value or an interpolated value that would be the middle value once the values are sorted.
The SQL MEDIAN() function typically operates on numeric expressions, but depending on the RDBMS, may also operate on other data types, such as datetime expressions.
Depending on the RDBMS, the MEDIAN() function can be used as a window function, as an aggregate function, or as both.
Some SQL relational database management systems (RDBMSs) have a MODE() function that returns the most frequently occurring value from all row values in a column.
The mode is the value that appears most frequently in a data set.
Continue readingSome SQL databases have a PERCENTILE_DISC() function that calculates a percentile based on a discrete distribution of a range of column values.
We specify the percentile to use when we call the function.
Depending on the DBMS, PERCENTILE_DISC() can be used as a window function, as an aggregate function, or as both.
The PERCENTILE_DISC() function always returns a value from the underlying data. This is in contrast to the PERCENTILE_CONT() function, which can interpolate between adjacent values to return a value that’s not in the underlying data.
Some relational database management systems (RDBMSs) have a PERCENTILE_CONT() function that calculates a percentile based on a continuous distribution across a range of column values.
We specify the percentile to use when we call the function.
Depending on the RDBMS, PERCENTILE_CONT() can be used as a window function or an aggregate function, or as both.
If you’re getting an error that reads “function mode() does not exist” in PostgreSQL, it could be that you’re using the wrong syntax with this function.
The mode() function requires a WITHIN GROUP clause, and we can get the above error if we remove that clause.
In this case, we can fix the error by adding a valid WITHIN GROUP clause.