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.
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 readingWhen 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.
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 readingThe following table contains a full list of the aggregate functions in MySQL.
Continue readingIn 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.
In MySQL, the VARIANCE() function returns the population standard variance of an expression.
If there are no matching rows, or if the expression is NULL, the function returns NULL.
The VARIANCE() function is a synonym for the standard SQL VAR_POP() function. In other words, they both do the same thing, but VARIANCE() is not standard SQL. Therefore, if you need to use standard SQL, use VAR_POP() instead.
In MySQL, the VAR_POP() function returns the population standard variance of an expression.
If there are no matching rows, or if the expression is NULL, VAR_POP() it returns NULL.
VAR_POP() is an aggregate function, and so it can be used with the GROUP BY clause.
The VAR_POP() function is standard SQL. There’s also a VARIANCE() function that does the same thing, but is not standard SQL.
In MySQL, the STDDEV_SAMP() function returns the sample standard deviation of a given expression. This is the square root of VAR_SAMP().
STDDEV_SAMP() is an aggregate function, and so it can be used with the GROUP BY clause.
If there are no matching rows, or if the expression is NULL, STDDEV_SAMP() returns NULL.
In MySQL, the STDDEV_POP() function returns the population standard deviation of a given expression.
There’s also a STD() and STDDEV() function, both of which do the same thing as STDDEV_POP().