Search code examples
postgresqlsumamazon-redshiftwindow-functionslooker

Table calculations in Looker with a group by condition

My dataset looks like the following (first four columns) -

Person Rate Count  isImportant  Results
A       $2    15    true        $2
B       $1    30    true        $1
B       $3    10    false       (3*10+2.5*25)/(10+25)
B       $2.5  25    false       (3*10+2.5*25)/(10+25)
D       $1.5  20    false       $1.5

How can I generate the fifth column, Results by grouping the columns, Person and isImportant and calculating the weighted average of the Rate and Count columns?

Solution

  • You can use window functions:

    select t.*,
    sum(rate * count) over(partition by person, isImportant)
        / sum(count) over(partition by person, isImportant) result
from mytable t