Question

# How to aggregate the measure values to one dimension values

• 6 replies
• 303 views

• Member
• 38 replies

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

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

Jack,

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

I tried your suggestion but it is returning this error:

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
Userlevel 7
+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

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