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

Knowledge Drop

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.                

Version history
Last update:
‎04-05-2021 09:04 AM
Updated by: