How to aggregate the measure values to one dimension values

Looker Q&A Looker
1

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.

2

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

3

Jack,

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

4

I tried your suggestion but it is returning this error:

5

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
6

@rohit_shoppertr 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

7

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

8

Could you share how you did the blend? I’m trying to do this exact same thing.