Knowledge Drop

SQL Error: "X is not a valid group by expression"

  • 5 April 2021
  • 0 replies
  • 1675 views

Userlevel 5
Badge
  • Looker Staff
  • 171 replies

Last tested: March 2021

 

This SQL error means that that database is trying to group on something that it can't. Usually, this means that there are aggregates in a dimension definition.

To troubleshoot, first try to isolate the field that throws the error. Then take a look at its LookML definition. One example of aggregation inside dimensions that could cause this is:

dimension: bad_dimension {type: numbersql: SUM(${other_dimension}) ;;}

The solution is to only do aggregations in measures. This example should instead be a measure:

measure: sum_measure {type: sumsql: ${other_dimension} ;;}

If you need aggregation to happen in a dimension, so that you can pivot or group by it for example, then see dimensionalizing a measure.

 

This content is subject to limited support.                


0 replies

Be the first to reply!

Reply