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.
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