
<h2 class="wp-block-heading"><strong>Segmented totals</strong></h2>



<p>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:</p>



<pre class="wp-block-code"><code>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</code></pre>



<p>In your favorite visualization tool, this will give a sales graph that looks like this:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-1-totals-blog.png" alt="Sales by Salesperson chart" class="wp-image-73576"/></figure>



<p>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.</p>



<h2 class="wp-block-heading"><strong>Bundling the query in a CTE</strong></h2>



<p>To get there, let’s start by taking our original query and wrapping it in a CTE:</p>



<pre class="wp-block-code"><code>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</code></pre>



<p>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.</p>



<h2 class="wp-block-heading"><strong>Adding in the totals</strong></h2>



<p>Now let’s add in the totals:</p>



<pre class="wp-block-code"><code>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</code></pre>



<p>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.</p>



<p>A sampling of lines from the result set looks like this:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-3-totals-blog.png" alt="Salesperson Revenue Totals chart" class="wp-image-73591"/></figure>



<p>For each month, in addition to each salesperson’s number, we now have the total number as well!</p>



<p>With a few tweaks in our visualization suite, we can visualize the individual performance and the total performance together like so:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-2-totals-blog.png" alt="Salesperson performance chart" class="wp-image-73581"/></figure>



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


Using CTEs and Unions to Compute Running Totals

SQL Superstar

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article