Tag: what is

MySQL IGNORE Clause Explained

Posted on by Ian

In MySQL, we can use the IGNORE clause in statements that change data in order to ignore certain errors that might occur had we not used it. When IGNORE is used, such errors are downgraded to warnings.

For example, we can use IGNORE in an INSERT statement to ignore any errors we might normally get if we tried to insert a NULL value into a NOT NULL column. In such a case, MySQL won’t return an error. Instead, it will deal with the issue in another way, and provide us with a warning.

If we have strict mode enabled, we can use IGNORE to force MySQL to act as though strict mode is disabled. However, IGNORE can also be used to downgrade certain errors regardless of the strict mode setting.

Continue reading →

MySQL GROUPING() Function Explained

Posted on by Ian

In MySQL, the GROUPING() function allows us to identity which rows have been generated by the WITH ROLLUP modifier of the GROUP BY clause.

By default, WITH ROLLUP outputs NULL as the value that represents all aggregate values. By this, I mean it doesn’t provide us with a nice easy to read label. It simply outputs NULL. This makes it more difficult for us to distinguish between normal rows and super aggregate rows that were generated by WITH ROLLUP.

Continue reading →

MySQL ANY_VALUE() Function Explained

Posted on by Ian

In MySQL, ANY_VALUE() is an aggregate function that allows us to include nonaggregated columns in the SELECT list when using the GROUP BY clause.

The ANY_VALUE() function is commonly used to resolve issues that arise when a query includes columns that are not part of the GROUP BY clause or aggregate functions. It can be useful in scenarios where we want to include descriptive columns in a grouped result set without affecting the grouping behaviour.

Continue reading →

Understanding the VAR_SAMP() Function in MySQL

Posted on by Ian

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.

Continue reading →

How VARIANCE() Works in MySQL

Posted on by Ian

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.

Continue reading →

Understanding the VAR_POP() Function in MySQL

Posted on by Ian

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.

Continue reading →

Understanding the STDDEV_SAMP() Function in MySQL

Posted on by Ian

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.

Continue reading →