Trouble creating pivot table without aggregate function

I have a view with two dimensions (serial_number and condition) and a measure (test_metric) of type: number. The data for these is from a table already in my database (my_table). In an explore I’d like to pivot on condition so that each distinct value of condition ultimately gets plotted as a different series. However I keep getting the following error:

ERROR: column "test_metric" must appear in the GROUP BY clause or be used in an aggregate function.

view: test_results {
sql_table_name: public.my_table;;

dimension: serial_number {
type: number
sql: ${TABLE}.serial_number ;;
}

dimension: condition {
type: string
sql: ${TABLE}.condition ;;
}

measure: test_metric {
type: number
sql: ${TABLE}.test_metric ;;
}
}

I’ve looked up this error and there appear to be a few answers but I’m having difficulty implementing them or they don’t seem applicable. For instance if it’s as simple as adding test_metric to the GROUP BY clause, how do I do that in lookml?

This seems like a really straight forward implementation where I want to plot data as is and not use an aggregate measure. 

Thanks in advance. 

0 1 972
1 REPLY 1

leobardor
Participant V

Hi Levers!

Instead of sharing you all looker docs to show you the differences between dimensions and measures, I will try to explain in my own words:

“Looker does not choose which column is a dimension and which one is a measure”

In this case, your table already has a summary of each serial_number ID, however, for looker, it is a dimension because looker is not applying any aggregation function.

You may need to use this field as a dimension or apply an aggregation function, for instance:

 measure: test_metric {
type: number
#SUM
sql: sum({TABLE}.test_metric) ;;
}

Hope it helps

Best Regards and happy testing,

Leo

Top Labels in this Space
Top Solution Authors