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

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

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

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

I tried your suggestion but it is returning this error:

Jack,

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

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