Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

How to aggregate the measure values to one dimension values

  • 20 December 2021
  • 6 replies
  • 303 views
R

Hi team,

 

My requirement says Sum of the field or  aggregate with sum function w.r.t columnA, 

Raw data:

 

ColumnA ColumnB  
A U 10
A V 10
B X 20
B Y 20

 

 

  

 

Required table:

ColumnA ColumnB Measure
A U 20
A V
B X 40
B Y

 

So, I want to group by Field on column A.

This topic has been closed for comments

6 replies

R

I am surprised that a group by clause is so difficult to create here. I have achieved it the required table by merge tables concept.

 

Thanks,

Rohit

Dawid
Userlevel 7
Badge +1

@rohit.shoppertrak you can’t “make” window functions in Table Calculations or even sql parameter in LookML, it has to be either pre-aggregated in your data model or done with a derived table

Dawid
JackLin

If you like, you can do this

 

WITH

  temp AS (

  SELECT

    DISTINCT ColumnA,

    SUM(ColumnC) AS result

  FROM

    example

  GROUP BY

    ColumnA ),

  fact AS (

  SELECT

    ex.*,

    ROW_NUMBER() OVER (PARTITION BY ColumnA ORDER BY ColumnA) AS rn,

  FROM

    example ex 

SELECT

  fact.ColumnA,

  fact.ColumnB,

  fact.ColumnC,

  temp.result

FROM

  fact

LEFT JOIN

  temp

ON

  fact.ColumnA = temp.ColumnA

  AND fact.rn = 1

 

Row ColumnA ColumnB ColumnC result  
1

A

U

10

20

  
2

A

V

10

 null  
3

B

X

20

40

  
4

B

Y

20

 null
R

I tried your suggestion but it is returning this error:

 

R

Jack,

Is there any other way to create Example2 Result without Derived table? Can we use table calculation of LookML?

JackLin

Hi,

I can only think of this solution. Hope it helps.

view: example2 {
  derived_table: {
    sql: select
    ColumnA,
    ColumnB,
    ColumnC,
    sum(ColumnC) OVER (PARTITION BY ColumnA) AS result
    from example ;;
  }

  dimension: ColumnA {}
  dimension: ColumnB {}
  dimension: ColumnC {}
  dimension: result {}
}

 

The simplest way is just use Column A (without ColumnB) to get the result, but I guess that’s not what you want to present in the table.

Jack

Terms & Conditions