Segmented totals
A common pattern in SQL analysis is to use multiple groupings to show grouped totals over time. For example, here’s a simple query that computes total sales by salesperson over time:
select
date_trunc('month', plan_start),
users.name,
sum(purchase_price)
from payment_plans join users
on users.id = payment_plans.sales_owner_id
group by 1, 2
In your favorite visualization tool, this will give a sales graph that looks like this:

This is a great graph for measuring salesperson performance, but it makes you work hard to figure out whether the overall business is growing. There ought to be a way to see both the individual and total performance from the same query in the same graph.
Bundling the query in a CTE
To get there, let’s start by taking our original query and wrapping it in a CTE:
with individual_performance as (
select
date('month', plan_start) m,
users.name salesperson,
sum(purchase_price) revenue
from payment_plans join users
on users.id = payment_plans.sales_owner_id
group by 1, 2
)
select
m,
salesperson,
revenue
from individual_performance
For now, this will give the same results. Note that we’ve named each column and made sure they’re selected in a certain order. That’ll matter as we proceed to the next step.
Adding in the totals
Now let’s add in the totals:
with individual_performance as (
select
date('month', plan_start) m,
users.name salesperson,
sum(purchase_price) revenue
from payment_plans join users
on users.id = payment_plans.sales_owner_id
group by 1, 2
)
select
m,
salesperson,
revenue
from individual_performance
union all
select
m,
'Total',
sum(revenue)
from individual_performance
group by 1
The last line goes back to our individual_performance CTE a second time, and this time selects the sum of the revenue each month. Using union all, we append that to the end of the resulting table.
A sampling of lines from the result set looks like this:

For each month, in addition to each salesperson’s number, we now have the total number as well!
With a few tweaks in our visualization suite, we can visualize the individual performance and the total performance together like so:

Voilà! You can use this trick with any segmented total query to add a running total.