cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

In many cases, a cohort grid can benefit from showing the rolled-up row or column totals. This can be accomplished within the query by UNION'ing the appropriately grouped columns.

Let's start by taking a look at the structure of our baseline cohort grid:

 

Community_Admin_0-1634738881492.png

 We have three columns:

  • Month
  • Source
  • Num_Users

Our goal will now be to add a "Total" row and column to our cohort grid that sums up the number of users for each.

Adding a Total Row:

First, we can start by building a new CTE to give us a total row (i.e. a row that shows the sum for each of our sources). Because this row will sum up the values by the source, we can use a 'Total' placeholder text for the month column. Our SQL for this CTE will look like:

(SQL)

, row_total AS

(

   select

     'Total'::text

     , source

     , sum(num_users)

   from

     users_by_source

   group by

    1

    , 2

)

It is important to note that we cast the 'Total' value to explicitly be text because we plan on UNION'ing this back to our original CTE. In order for this to succeed, we would also want to make sure to cast our original "month" conversion in the users_by_source CTE to also be text. Now, our final query and result will look like:

 

Community_Admin_1-1634738881513.png

 

Adding a Total Column:

We can now use similar logic for adding in a total column. But instead of selecting and grouping by the source, we'll want to select and group by our month column:

(SQL)

, total_column AS

(

   select

     month

     , 'Total'::text

     , sum(num_users)

   from

     users_by_source

   group by

     1

     , 2

)

Adding this into our previous query, we'll now have:

 

Community_Admin_2-1634738881486.png

You'll notice that the ordering doesn't come out quite the way we're aiming for. This is due to the inconsistent nature of ordering in SQL if we haven't explicitly added an ORDER BY statement. We'll now account for this in our final step.

Ordering Total Row/Column:

The simplest way to approach this is to add one more static, numeric column into each of our CTE's. We'll then use this column to define our final ordering. The important factors to keep in mind here are:

  • We want the 'Total' columns to appear at the end, so the column we add into their CTE's should have a larger value to order by
  • Accordingly, our baseline CTE ("users_by_source") should have a smaller value than the equivalent column in our total CTE's
  • Because our "Total" and now "Ordering" columns are going to be static, we don't necessarily have to include them in our grouping. This is why the CTE's in the SQL below only group by the non-static columns. But, it's also acceptable to do: "group by 1,2,4" in each CTE.

The final query with this new "Ordering" column is shown below:

(SQL)

with

  users_by_source as (

    select

      ([created_at:month])::text as month

      , source

      , count(1) as num_users

      , 1 as ordering

    from

      users

    group by

      1

      , 2

  )

 

  , total_row as (

    select

      'Total'::text

      , source

      , sum(num_users)

      , 2 as ordering

    from

      users_by_source

    group by

      2

  )

 

  , total_column as (

    select

      month

      , 'Total'::text

      , sum(num_users)

      , 2 as ordering

    from

      users_by_source

    group by

      1

  )

 

, roll_up as (

  select * from users_by_source

  union

  select * from total_row

  union

  select * from total_column

)

 

select * from roll_up

order by ordering, month

And voilà! Our final display now properly places our 'Total' row and column at the end of their respective groupings:

Community_Admin_3-1634738881440.png

Was this article helpful?

Version history
Last update:
‎10-20-2021 07:09 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request