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 readingTag: aggregate functions
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.
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.
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.
Overview of the MODE() Function in PostgreSQL
PostgreSQL has an ordered-set aggregate function called mode() that allows us to get the mode from a given column.
The mode is the most frequently occurring value.
Null values are ignored, so if null occurs the most, the mode() function will return the second most common value.
Create a Running Total in SQL
We can use SQL to create a running total of a given column. By this I mean, we can create a column that calculates the cumulative sum of a given column.
The running total/cumulative sum is the sum of all rows up until the current row. The result is that the sum increases (or decreases in the case of negative values) with each row returned in the result set. Each row’s value is added to the cumulative amount from all prior rows, so for any given row, we get the total sum for all rows up to that point – the “running total”.
Continue readingCalculate a Grand Total using a Window Function in SQL
When we use an OVER() clause to create a window function in SQL, we often use a PARTITION BY clause to partition the results. This can be handy if we want to do stuff like calculate subtotals.
But we can also use an empty OVER clause to calculate a grand total.
We might want to do this if we’re already using another OVER clause to calculate subtotals, but we also want a column to provide the grand total.
Calculate Subtotals using a Window Function in SQL
We can use the OVER() clause to create window functions in our SQL queries. A window function can be a useful tool that allows us to do things like compute moving averages, rank items, calculate cumulative sums, and much more.
Another common task for a window function might be to compute subtotals.
Continue readingMySQL Aggregate Functions (Full List)
The following table contains a full list of the aggregate functions in MySQL.
Continue readingUnderstanding the VAR_SAMP() Function in MySQL
In MySQL, the VAR_SAMP() function returns the sample variance of an expression. The denominator is the number of rows minus one.
If there are no matching rows, or if the expression is NULL, VAR_SAMP() returns NULL.
VAR_SAMP() is an aggregate function, and so it can be used with the GROUP BY clause.
The VAR_SAMP() function can also be used as a window function.