Calculate sum of column and repeat value across all rows.

[davdic77]

Hello,
I am trying to go from this:

COL1    COL2
ABC     1
DEF     2
GHI     3

to adding a sum of Col2 to Col3, but so it displays across all rows.

COL1    COL2   COL3
ABC     1      6
DEF     2      6
GHI     3      6

Now, in SQL I can do that no problem:

SELECT *
  ,(SELECT SUM(Col2) FROM Your_Table) Col3 
FROM Your_Table

However, I am not sure how I can accomplish the same without converting my Explore into SQL, which kind of defeats the purpose of having an Explore.
Is it possible to do something like this in a derived table block, with referencing ML objects? Even if I have to convert the explore into a bunch of joined views, that would be better than straight SQL.

[swan]

In the field list on the left of your Explore click on “Custom Fields”. Then click “New”, and select “Table Calculation”
In the text area add

sum(${Col2})

…give the field a name (“Col3” in your example), add formatting, and then save.

This should give you exactly what you are looking for.

[davdic77]

Is there a way to define a Table Calculation like this via LookML?

[michael_zearn]

Depending on your SQL dialect you may be able to use a window function, like:

  measure: col2_window_total {
    sql: sum(${col2}) over() ;;
  }

(works on my Redshift connection)

[swan]

Yes, a window function like the one explained by Michael will work on Redshift, Bigquery, or Snowflake

Be sure not to include type: sum in your measure declaration or Looker will output the SQL as sum(sum(col2) over ()) and throw an error.

[Rodrigo_Pizzano]

Depending on your SQL dialect you may be able to use a window function, like:

  measure: col2_window_total {

    sql: sum(${col2}) over() ;;

  }

(works on my Redshift connection)

Thank you! This is exactly what I needed. I didn’t know that sum() over() was posible in Look ML.

[fajaribnuf]

Hi @swan @michael_zearn @Rodrigo_Pizzano, thank you for your answer! This helped me to get the column percent of total by each category.