If you’re getting MySQL error 3582, which reads something like “Window ‘w1’ has a frame definition, so cannot be referenced by another window“, it’s probably because you’re referencing a named window that has a frame clause.
This can happen when we use the WINDOW clause to define a named window for a window function, and we include a frame clause in that window. When we then try to reference that window, MySQL tells us that we can’t, due to the frame clause.
Sometimes when we run SQL queries we might want to group the results by a given column. A common way to do this is with the GROUP BY clause.
But sometimes the GROUP BY clause isn’t enough.
Sometimes we want the results presented in a way that the GROUP BY clause doesn’t cater for. The GROUP BY is perfect if we want to collapse groups of rows into a single row, so that we can get an aggregate value for each set of collapsed rows. But sometimes we don’t want this. Sometimes we want to see all of the rows, as well as the aggregate values.
This is where the PARTITION BY clause comes in.
Sometimes when we use the SQL GROUP BY clause to group queries, it would be nice to have totals and subtotals included in the query results.
For example, if our query returns cities and their populations, grouped by the city’s district and perhaps also grouped by country, it would be nice to include subtotals for each district (based on the aggregate populations of all cities in the district). And it might also be nice to have the total population of each country, based on the same aggregate data.
Another way of putting it is that we want to get a summary of the summary. Or an aggregate of the aggregate. This is sometimes referred to as “super aggregate”.
Fortunately, in MySQL we can use the WITH ROLLUP modifier of the GROUP BY clause to achieve exactly that.
If you’re getting MySQL error 3583, which reads something like “Window ‘<unnamed window>’ cannot inherit ‘wf’ since both contain an ORDER BY clause“, it’s probably because you’re using an ORDER BY clause when referring to a named window that also has its own ORDER BY clause.
To fix this issue, don’t use the ORDER BY clause when referring to a named window that already has an ORDER BY clause in its definition.
In MySQL, the WINDOW clause is an optional clause that we can use to create a named window. The named window can then be referred to from a window function.
Many SQL developers define their window functions directly in the OVER clause. But that’s not the only way to do it. We can also define them in a WINDOW clause, and then refer to them in the OVER clause.
When we define the window function in a WINDOW clause, we name it. When we do this, we can refer to that name from the OVER clause. This eliminates the need to include the definition directly inside the OVER clause.
When running SQL database queries, sometimes we need to use a window function in order to get the results we’re looking for. A window function is an aggregate-like function that enables partitioning and ordering of data within a result set.
The OVER clause is what enables us to create a window function.
The examples below demonstrate how we can incorporate the OVER clause in our SQL queries.
If we want to remove a CHECK constraint from a table, but we don’t want to drop the table or column, we can use the ALTER TABLE statement with DROP CHECK.
Once the constraint has been dropped, data can be entered into the table without having to conform to the rules of the CHECK constraint.
If you’re getting MySQL error 1364, that reads something like Field 'column_name' doesn't have a default value, where column_name is the name of a column in your table, it’s probably because you’re trying to perform an operation that retrieves the column’s default value, but it doesn’t have a default value.
This can happen when the column is set to NOT NULL and hasn’t been defined with an explicit default value.
The solution to this issue will depend on what we’re trying to do. Below are examples of how to fix this error.
Continue readingIn MySQL, we can use the sys.quote_identifier() function to quote a string to produce a result that can be used as an identifier in a SQL statement.
The string is returned enclosed by backticks (`), with each instance of a backtick doubled.
This function can be handy when a value to be used as an identifier is a reserved word or contains backtick characters.
Continue readingThe information schema is a standard set of read-only views that provide metadata about objects in a database. These views are typically accessed by qualifying the view name with INFORMATION_SCHEMA (for example INFORMATION_SCHEMA.TABLES). But before we do this, we need to know which views are available in the first place.