Tag: how to

How to Add Totals and Subtotals When Using GROUP BY in MySQL

Posted on by Ian

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.

Continue reading →

Fix ERROR 3583 “Window ‘…’ cannot inherit ‘…’ since both contain an ORDER BY clause” in MySQL

Posted on by Ian

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.

Continue reading →

Introduction to the OVER Clause in SQL

Posted on by Ian

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.

Continue reading →

5 Ways to Fix Error 1364 “Field ‘…’ doesn’t have a default value” in MySQL

Posted on by Ian

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 reading →

Understanding Window Functions in SQL

Posted on by Ian

Window functions can be a useful tool when writing SQL queries. They allow us to include aggregate data across multiple rows without getting those pesky errors that sometimes occur when we try to use an aggregate function in the wrong way.

In this article, I aim to provide a simple overview of window functions and how they can be used to provide a more useful result set when running SQL queries.

Continue reading →

2 Ways to Add a DEFAULT Clause to an Existing Column in MySQL

Posted on by Ian

If you have an existing column in MySQL that you want to add a DEFAULT clause to, the following example may help.

To explicitly add a DEFAULT clause to an existing column in MySQL, we can use the ALTER TABLE statement along with the MODIFY clause.

We can also add a DEFAULT clause implicitly by defining a column as nullable. When we do this, MySQL implicitly adds its own DEFAULT clause.

Continue reading →