Tag: aggregation

Create a Running Total in SQL

Posted on by Ian

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

Calculate a Grand Total using a Window Function in SQL

Posted on by Ian

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.

Continue reading →

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 →

How to Add Headings for WITH ROLLUP in MySQL

Posted on by Ian

MySQL provides us with the WITH ROLLUP modifier for the GROUP BY function for generating super aggregate data.

But by default, this modifier doesn’t label its data. By this I mean, it doesn’t provide us with meaningful labels for the rows that it generates. It simply uses NULL in those rows.

Maybe you’ve encountered this and are now looking for a solution.

Below is a handy little technique we can use to replace NULL with our own headings for these rows.

Continue reading →

6 Ways to Fix Error 1055 “Expression … of SELECT list is not in GROUP BY clause and contains nonaggregated column…” in MySQL

Posted on by Ian

If you’ve been using MySQL for any decent amount of time, it’s likely you’ll be familiar with error 1055 that reads something like “Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column…“, where #N is the expression number of an expression/column in your SELECT list.

This error can occur when we include a column in the SELECT list, but we omit it from the GROUP BY clause.

There are several ways we can go about resolving this issue. Below are six options for dealing with this issue.

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 →