Introduction to the PERCENTILE_DISC() Function in SQL

Some 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.

Continue reading

Overview of the PERCENTILE_CONT() Function in SQL

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.

Continue reading

How to Fix the Error: “The function ‘LEAD’ must have an OVER clause with ORDER BY” in SQL Server

If you’re getting an error message that reads “The function ‘LEAD’ 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 LEAD() function.

The LEAD() 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.

Continue reading

Understanding the LAST_VALUE() Function in SQL

In SQL databases, LAST_VALUE() is a window function that returns the last value in an ordered set of values. It allows us to get a value from the last row of a query result set or partition.

You may need to explicitly set the window frame if you want LAST_VALUE() to return the actual last value from the partition or result set. That’s because in many/most DBMSs, the default window frame ends with the current row.

Continue reading

How to Fix Error “function mode() does not exist” in PostgreSQL

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.

Continue reading